Why does the function TO_CHAR get invoked for each row when not wrapped in a subquery?

Question

I am working in an Oracle to PostgreSQL migration project. Today I found what I think is a PostgreSQL bug. I wonder whether I missed something in my experimentation.

The following is my test code.

create table t2 as
select i as c1
     , '12000103' as c2
 from generate_series(1,1000000) a(i);

    explain (analyze, verbose)
    select *
  from t2
 where c2 = to_char(current_date, 'yyyymmdd');

After running the above SQL statement I got this execution plan.

 Gather  (cost=1000.00..14739.43 rows=1 width=13) (actual time=950.342..954.845 rows=0 loops=1)
   Output: c1, c2
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on postgres_air.t2  (cost=0.00..13739.33 rows=1 width=13) (actual time=929.924..929.925 rows=0
loops=3)
         Output: c1, c2
         Filter: (t2.c2 = to_char((CURRENT_DATE)::timestamp with time zone, 'yyyymmdd'::text))
         Rows Removed by Filter: 333333
         Worker 0:  actual time=917.776..917.776 rows=0 loops=1
         Worker 1:  actual time=925.052..925.053 rows=0 loops=1
 Planning Time: 0.482 ms
 Execution Time: 954.978 ms
(12 rows)

Compared with Oracle's elapsed time, the execution time in PostgreSQL was too long. I couldn't find out the reason why PostgreSQL was so slow in this simple query. Coincidently I changed where clause.

 explain (analyze, verbose)
select *
  from t2
 where c2 = (select to_char(current_date, 'yyyymmdd'));

 Seq Scan on postgres_air.t2  (cost=0.02..17906.02 rows=1000000 width=13) (actual time=236.536..236.538 rows=0 loops=1)
   Output: t2.c1, t2.c2
   Filter: (t2.c2 = $0)
   Rows Removed by Filter: 1000000
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.02 rows=1 width=32) (actual time=0.025..0.027 rows=1 loops=1)
           Output: to_char((CURRENT_DATE)::timestamp with time zone, 'yyyymmdd'::text)
 Planning Time: 0.460 ms
 Execution Time: 236.892 ms

The elapsed time dropped from 954 ms to 236 ms. My question is :

1. Why is the revised query more performant?
2. Is this just a PostgreSQL bug?
3. If this is a PostgreSQL bug, should I use a subquery whenever I use a PostgreSQL internal function?

Answer 1

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);