From: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: need clarification on CTE/join |
Date: | 2021-03-24 03:09:13 |
Message-ID: | CADX_1abk-uK6LNrgj_azYv1phPw=oGrfYhOOH9FMJYuQsRcT8Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Wed, Mar 24, 2021 at 3:22 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Tue, Mar 23, 2021 at 6:45 PM Marc Millas <marc(dot)millas(at)mokadb(dot)com>
> wrote:
>
>> So.. I would like to understand the "why" of this behaviour, ie. the
>> change of order when I do the cast.
>>
>
> I believe the "why" is immaterial here. Your queries do not contain order
> by so your results are unordered - even if there appears to be an apparent
> ordering for any particular result.
>
> David J.
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-03-24 03:21:42 | Re: need clarification on CTE/join |
Previous Message | David G. Johnston | 2021-03-24 02:22:28 | Re: need clarification on CTE/join |