From: | hao li <shell0fly(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Default Inline CTE makes JOIN slower |
Date: | 2021-03-29 13:39:25 |
Message-ID: | CAPcidOOfE5phpG+f_Qp9HAMr8cPbmLUef8SFpCPgXPvyUubv+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
I have this example query in my code
create or replace function slow_int() returns integer language sql stable
as $$
select pg_sleep(1);
select 1;
$$;
explain analyze WITH s AS (
SELECT * FROM (values (1), (2), (3)) s(a)
),
slow_int AS (
SELECT slow_int()
)
SELECT * FROM s
JOIN slow_int ON true;
and I got this explain analyze output which execute slow_int() three times,
you can see the execution time is 3s, not 1s.
Values Scan on "*VALUES*" (cost=0.00..0.79 rows=3 width=8) (actual
time=1002.468..3005.399 rows=3 loops=1)
Planning Time: 0.326 ms
Execution Time: 3005.512 ms
It happens because PG12 automatically inline CTE, and if I force it to be
materialized, I can get previous execution time.
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2021-03-29 14:07:10 | Re: Default Inline CTE makes JOIN slower |
Previous Message | Pantelis Theodosiou | 2021-03-29 09:17:22 | Re: BUG #16945: where value in (null) set results inconsistent |