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

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)