CTE Materialization
Hi,
when switching to postgres 14 (from 11) I'm having some slow queries
because of inlining of CTE's.
I know I can get the same result as with PG11 when adding MATERIALIZED to
the cte, but the same application also needs to be able to run on older
postgres versions, so that is a no-go.
Is there any other way that I can have materialized cte's in PG14 while
still be compatible with older PG versions?
Much appreciated,
Paul
PS please cc me when answering
On Thursday, December 2, 2021, Paul van der Linden <
paul.doskabouter@gmail.com> wrote:
Hi,
when switching to postgres 14 (from 11) I'm having some slow queries
because of inlining of CTE's.
I know I can get the same result as with PG11 when adding MATERIALIZED to
the cte, but the same application also needs to be able to run on older
postgres versions, so that is a no-go.
Is there any other way that I can have materialized cte's in PG14 while
still be compatible with older PG versions?
Much appreciated,
The usual anti-inlining hack is to add an “offset 0” to the query. Haven’t
tried it in 14 myself though.
David J.
Thanks a lot, completely forgot that one!
Gonna test that tomorrow...
On Thu, Dec 2, 2021 at 11:34 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Thursday, December 2, 2021, Paul van der Linden <
paul.doskabouter@gmail.com> wrote:Hi,
when switching to postgres 14 (from 11) I'm having some slow queries
because of inlining of CTE's.
I know I can get the same result as with PG11 when adding MATERIALIZED to
the cte, but the same application also needs to be able to run on older
postgres versions, so that is a no-go.
Is there any other way that I can have materialized cte's in PG14 while
still be compatible with older PG versions?
Much appreciated,The usual anti-inlining hack is to add an “offset 0” to the query.
Haven’t tried it in 14 myself though.David J.
It did indeed work as expected.
Took the query down from over 18 hours to 20 minutes, so a huge win!
Paul
On Thu, Dec 2, 2021 at 11:34 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Thursday, December 2, 2021, Paul van der Linden <
paul.doskabouter@gmail.com> wrote:Hi,
when switching to postgres 14 (from 11) I'm having some slow queries
because of inlining of CTE's.
I know I can get the same result as with PG11 when adding MATERIALIZED to
the cte, but the same application also needs to be able to run on older
postgres versions, so that is a no-go.
Is there any other way that I can have materialized cte's in PG14 while
still be compatible with older PG versions?
Much appreciated,The usual anti-inlining hack is to add an “offset 0” to the query.
Haven’t tried it in 14 myself though.David J.
I beg your pardon.
The problem is more or less clear to me, but the solution is not. What does
the "hack is to add an "offset 0" to the query" suggest? Thank you.
--
Regards, Dmitry!
вт, 7 дек. 2021 г. в 10:20, Paul van der Linden <paul.doskabouter@gmail.com
Show quoted text
:
It did indeed work as expected.
Took the query down from over 18 hours to 20 minutes, so a huge win!Paul
On Thu, Dec 2, 2021 at 11:34 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:On Thursday, December 2, 2021, Paul van der Linden <
paul.doskabouter@gmail.com> wrote:Hi,
when switching to postgres 14 (from 11) I'm having some slow queries
because of inlining of CTE's.
I know I can get the same result as with PG11 when adding MATERIALIZED
to the cte, but the same application also needs to be able to run on older
postgres versions, so that is a no-go.
Is there any other way that I can have materialized cte's in PG14 while
still be compatible with older PG versions?
Much appreciated,The usual anti-inlining hack is to add an “offset 0” to the query.
Haven’t tried it in 14 myself though.David J.
On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов <firstdismay@gmail.com> wrote:
I beg your pardon.
The problem is more or less clear to me, but the solution is not. What
does the "hack is to add an "offset 0" to the query" suggest? Thank you.
A subquery with a LIMIT clause cannot have where clause expressions in
upper parts of the query tree pushed down it without changing the overall
query result - something the planner is not allowed to do. For the hack,
since adding an actual LIMIT clause doesn't make sense you omit it, but
still add the related OFFSET clause so the planner still treats the
subquery as a LIMIT subquery. And since you don't want to skip any rows
you specify 0 for the offset.
David J.
This one quite nicely explains it:
https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subquery
On Wed, Dec 8, 2021 at 3:14 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:
Show quoted text
On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов <firstdismay@gmail.com>
wrote:I beg your pardon.
The problem is more or less clear to me, but the solution is not. What
does the "hack is to add an "offset 0" to the query" suggest? Thank you.A subquery with a LIMIT clause cannot have where clause expressions in
upper parts of the query tree pushed down it without changing the overall
query result - something the planner is not allowed to do. For the hack,
since adding an actual LIMIT clause doesn't make sense you omit it, but
still add the related OFFSET clause so the planner still treats the
subquery as a LIMIT subquery. And since you don't want to skip any rows
you specify 0 for the offset.David J.
Спасибо!
--
С уважением, Дмитрий!
ср, 8 дек. 2021 г. в 22:58, Paul van der Linden <paul.doskabouter@gmail.com
Show quoted text
:
This one quite nicely explains it:
https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subqueryOn Wed, Dec 8, 2021 at 3:14 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов <firstdismay@gmail.com>
wrote:I beg your pardon.
The problem is more or less clear to me, but the solution is not. What
does the "hack is to add an "offset 0" to the query" suggest? Thank you.A subquery with a LIMIT clause cannot have where clause expressions in
upper parts of the query tree pushed down it without changing the overall
query result - something the planner is not allowed to do. For the hack,
since adding an actual LIMIT clause doesn't make sense you omit it, but
still add the related OFFSET clause so the planner still treats the
subquery as a LIMIT subquery. And since you don't want to skip any rows
you specify 0 for the offset.David J.
On Thu, 9 Dec 2021 at 10:29, Paul van der Linden <paul.doskabouter@gmail.com>
wrote:
This one quite nicely explains it:
https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subquery
Given indexes applicable to multiple expressions in a WHERE condition, how
does postgres decide which index is most beneficial to use?
The author of that SO post tried to adjust the default statistics target,
presumably to convince postgres to use the faster primary key index,
instead of the slower gist index on the hstore values, but this didn't work.
thanks,
richard
Show quoted text
On Wed, Dec 8, 2021 at 3:14 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов <firstdismay@gmail.com>
wrote:I beg your pardon.
The problem is more or less clear to me, but the solution is not. What
does the "hack is to add an "offset 0" to the query" suggest? Thank you.A subquery with a LIMIT clause cannot have where clause expressions in
upper parts of the query tree pushed down it without changing the overall
query result - something the planner is not allowed to do. For the hack,
since adding an actual LIMIT clause doesn't make sense you omit it, but
still add the related OFFSET clause so the planner still treats the
subquery as a LIMIT subquery. And since you don't want to skip any rows
you specify 0 for the offset.David J.