From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | hao li <shell0fly(at)gmail(dot)com> |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Default Inline CTE makes JOIN slower |
Date: | 2021-03-29 14:07:10 |
Message-ID: | CAFj8pRCyavgQu38EJB_LGjU+yvC=HR-54E0bYdFRxSXobJcRig@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi
po 29. 3. 2021 v 15:39 odesílatel hao li <shell0fly(at)gmail(dot)com> napsal:
> 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.
>
sure. It is expected behaviour - and reason why there is new clause
MATERIALIZED
Regards
Pavel
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2021-03-30 09:18:30 | BUG #16946: It may be that the content of the document is missing |
Previous Message | hao li | 2021-03-29 13:39:25 | Default Inline CTE makes JOIN slower |