Thread: 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:
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:
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.
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>:
It did indeed work as expected.Took the query down from over 18 hours to 20 minutes, so a huge win!PaulOn 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:
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>:
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
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.