Re: need clarification on CTE/join - Mailing list pgsql-general

On Tuesday, March 23, 2021, Marc Millas <marc.millas@mokadb.com> wrote:

Hi,

I cannot agree.

I did an explain analyze with and without the cast: its extremely different:

postgres=# explain analyze with numb as(select ceiling(2582*random())::int rand, generate_series(1,5) as monnum) select monnum, prenom from numb,prenoms where numb.rand=prenoms.id;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.33..37.65 rows=5 width=11) (actual time=0.059..0.147 rows=5 loops=1)
   CTE numb
     ->  ProjectSet  (cost=0.00..0.05 rows=5 width=8) (actual time=0.021..0.022 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
   ->  CTE Scan on numb  (cost=0.00..0.10 rows=5 width=8) (actual time=0.023..0.025 rows=5 loops=1)
   ->  Index Scan using prenoms_pkey on prenoms  (cost=0.28..7.50 rows=1 width=11) (actual time=0.024..0.024 rows=1 loops=5)
         Index Cond: (id = numb.rand)
 Planning Time: 0.111 ms
 Execution Time: 0.201 ms
(9 lignes)


postgres=# explain analyze with numb as(select ceiling(2582*random()) rand, generate_series(1,5) as monnum) select monnum, prenom from numb,prenoms where numb.rand=prenoms.id;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=0.21..54.59 rows=65 width=11) (actual time=0.105..0.291 rows=5 loops=1)
   Hash Cond: ((prenoms.id)::double precision = numb.rand)
   CTE numb
     ->  ProjectSet  (cost=0.00..0.05 rows=5 width=12) (actual time=0.002..0.003 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
   ->  Seq Scan on prenoms  (cost=0.00..40.82 rows=2582 width=11) (actual time=0.011..0.131 rows=2582 loops=1)
   ->  Hash  (cost=0.10..0.10 rows=5 width=12) (actual time=0.012..0.012 rows=5 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  CTE Scan on numb  (cost=0.00..0.10 rows=5 width=12) (actual time=0.004..0.005 rows=5 loops=1)
 Planning Time: 0.070 ms
 Execution Time: 0.313 ms
(11 lignes)

If I’m reading that correctly since prenoms.id is an integer if you don’t cast the ceiling(random) away from double you cannot use the index since its not the same type - the integer has to become double, not the reverse.  So you get a different execution and thus different result ordering since the executor doesn’t have to care about row order.

David J.