The question is why the subquery in the second example is executed only once, instead of once for each row, which accounts for the difference in execution time.

The reason is that the subquery is not “correlated” with the surrounding query, that is, it does not reference anything from the outer query. The optimizer then executes the query only once (InitPlan) and uses the result doe each row.

One can argue (and it has been argued before) that this behavior is wrong, but with equal right you could say that if you execute it as a subquery, you want it executed independently. At any rate, this is known and accepted behavior, and I don't think you will be able to convince PostgreSQL that this is a bug.

If you want the subquery to be executed for each row, reference the outer query:

SELECT *
FROM t2
WHERE c2 = (SELECT CASE WHEN c2 IS NOT NULL
                        THEN to_char(current_date, 'yyyymmdd')
                   END);

The question is why the subquery in the second example is executed only once, instead of once for each row, which accounts for the difference in execution time.

The reason is that the subquery is not “correlated” with the surrounding query, that is, it does not reference anything from the outer query. The optimizer then executes the query only once (InitPlan) and uses the result for each row.

One can argue (and it has been argued before) that this behavior is wrong, but with equal right you could say that if you execute it as a subquery, you want it executed independently. At any rate, this is known and accepted behavior, and I don't think you will be able to convince PostgreSQL that this is a bug.

If you want the subquery to be executed for each row, reference the outer query:

SELECT *
FROM t2
WHERE c2 = (SELECT CASE WHEN c2 IS NOT NULL
                        THEN to_char(current_date, 'yyyymmdd')
                   END);

The second query should actually be slower, since it doesn't use parallel query. If it is faster, the reason is probably that more of your data happen to be in cache.

With a regular B-tree index on c2, this query would be very fast.

The question is why the subquery in the second example is executed only once, instead of once for each row, which accounts for the difference in execution time.

The reason is that the subquery is not “correlated” with the surrounding query, that is, it does not reference anything from the outer query. The optimizer then executes the query only once (InitPlan) and uses the result doe each row.

One can argue (and it has been argued before) that this behavior is wrong, but with equal right you could say that if you execute it as a subquery, you want it executed independently. At any rate, this is known and accepted behavior, and I don't think you will be able to convince PostgreSQL that this is a bug.

If you want the subquery to be executed for each row, reference the outer query:

SELECT *
FROM t2
WHERE c2 = (SELECT CASE WHEN c2 IS NOT NULL
                        THEN to_char(current_date, 'yyyymmdd')
                   END);