CTE optimization fence
Hello,
I am running into performance issues with large CTE "WITH" queries (just
for selecting, not updating). I was surprised to find that the queries
run much faster if I convert the CTEs to subqueries. From googling, I
see that this is due to CTE acting as an optimization fence in PG.
Unfortunately due to the application I'm dealing with, converting all
CTE queries to subquery model is not feasible. Plus, the readability of
CTE is a big bonus.
I see there was some discussion last year about removing the CTE
optimization fence (e.g.
http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't
find anything more recent. Does anyone know if this is still under
consideration? For what it's worth, I would love some way to make CTEs
inlined/optimized.
Thank you very much to the developers for a truly amazing database system.
Thanks
Guy
Guy Burgess <guy@burgess.co.nz> writes:
I see there was some discussion last year about removing the CTE
optimization fence (e.g.
http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't
find anything more recent. Does anyone know if this is still under
consideration?
Nothing's actually happened since then ... it's still under consideration,
but we have to settle on a way of controlling it.
regards, tom lane
Tom Lane schrieb am 27.06.2018 um 05:48:
I see there was some discussion last year about removing the CTE
optimization fence (e.g.
http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't
find anything more recent. Does anyone know if this is still under
consideration?but we have to settle on a way of controlling it.
+1 from me.
I am running more and more into situations where people consider this a bug rather than a feature.
FWIW, I think a GUC that switches between the current (mostly unwanted, at least surprising)
way and one where the CTE is optimized together with the main query would suit "most" people.
For sake of compatibility this could default to the current behaviour
I don’t think the fence analogy works. Try wall (a la Berlin).
I count myself amongst those who thought “Ooh this little CTE will garner just what the rest of the query needs”. Only to find the planner didn’t groc that optimization.
Is it a matter of deciding to trust the SQLer and runtime analyzing the CTE product before continuing? As an SQLer I have doubts about that precept but without _some_ change in attack CTEs approach fluff.
Show quoted text
On Jun 26, 2018, at 11:45 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Tom Lane schrieb am 27.06.2018 um 05:48:
I see there was some discussion last year about removing the CTE
optimization fence (e.g.
http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't
find anything more recent. Does anyone know if this is still under
consideration?but we have to settle on a way of controlling it.
+1 from me.
I am running more and more into situations where people consider this a bug rather than a feature.
FWIW, I think a GUC that switches between the current (mostly unwanted, at least surprising)
way and one where the CTE is optimized together with the main query would suit "most" people.For sake of compatibility this could default to the current behaviour
På onsdag 27. juni 2018 kl. 07:45:25, skrev Thomas Kellerer <spam_eater@gmx.net
<mailto:spam_eater@gmx.net>>:
Tom Lane schrieb am 27.06.2018 um 05:48:
I see there was some discussion last year about removing the CTE
optimization fence (e.g.
http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't
find anything more recent. Does anyone know if this is still under
consideration?but we have to settle on a way of controlling it.
+1 from me.
I am running more and more into situations where people consider this a bug
rather than a feature.
FWIW, I think a GUC that switches between the current (mostly unwanted, at
least surprising)
way and one where the CTE is optimized together with the main query would
suit "most" people.
For sake of compatibility this could default to the current behaviour
+1 from me. The default should be "no fence" for sake of least surprise I
think. Documenting the change would be sufficient.
I hope this will be picked up in the comming V12-cycle.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
On 06/27/2018 09:58 AM, Andreas Joseph Krogh wrote:
but we have to settle on a way of controlling it.
+1 from me.
I am running more and more into situations where people consider
this a bug rather than a feature.FWIW, I think a GUC that switches between the current (mostly
unwanted, at least surprising)
way and one where the CTE is optimized together with the main query
would suit "most" people.For sake of compatibility this could default to the current behaviour
+1 from me. The default should be "no fence" for sake of least surprise
I think. Documenting the change would be sufficient.
I hope this will be picked up in the comming V12-cycle.
FYI this subject has been discussed in this thread :
/messages/by-id/5351711493487900@web53g.yandex.ru
Regards,
På onsdag 27. juni 2018 kl. 11:44:05, skrev Adrien NAYRAT <
adrien.nayrat@anayrat.info <mailto:adrien.nayrat@anayrat.info>>:
On 06/27/2018 09:58 AM, Andreas Joseph Krogh wrote:
>
> but we have to settle on a way of controlling it.+1 from me.
I am running more and more into situations where people consider
this a bug rather than a feature.FWIW, I think a GUC that switches between the current (mostly
unwanted, at least surprising)
way and one where the CTE is optimized together with the main query
would suit "most" people.For sake of compatibility this could default to the current behaviour
+1 from me. The default should be "no fence" for sake of least surprise
I think. Documenting the change would be sufficient.
I hope this will be picked up in the comming V12-cycle.
FYI this subject has been discussed in this thread :
/messages/by-id/5351711493487900@web53g.yandex.ru
Regards,
I know. I hate the INLINE proposal and hope default-behaviour will be like in
other DBs, inline like sub-query as default. GUC for preserving fence is what I
hope will happen.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
On 2018-Jun-27, Andreas Joseph Krogh wrote:
I know. I hate the�INLINE proposal and hope default-behaviour will be like in
other DBs, inline like sub-query as default. GUC for preserving fence is what I
hope will happen.
I am probably biased ;-) but I like my proposal #1 in this email
/messages/by-id/20170503173305.fetj4tz7kd56tjlr@alvherre.pgsql
where you have to mark queries were the current behavior is desired
(WITH MATERIALIZED), and leave all other queries alone. I don't
remember any dissent on that.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services