3 ms·

I'll play devil's advocate: the optimization fence on CTEs is a godsend. The query planner is often the wrong kind of smart. A big table can get autovacuumed in

I'll play devil's advocate: the optimization fence on CTEs is a godsend. The query planner is often the wrong kind of smart. A big table can get autovacuumed in the middle of the night, cause the planner to use a new query plan, and a query that used to be instantaneous takes minutes. If you know the query plan you want, you can usually force it using CTEs. Really I'd be happier without a query planner at all.

jordanthoms 8y ago
Don't think I'd like having no query planner, most of the time it does the right thing. But having the ability to more easily correct it when it goes wrong would be nice (arguably postgres already supports the ideal way of doing this with it's custom statistics, though sometimes it's easier to have a more manual knob).
munk-a 8y ago
Sorry, to clarify the "(mandatory)" in the comment above, pg12 will be making CTEs optionally optimization fences, so people who want to hand-plan their query still can, but people who wish to use CTEs for organization and clarity will be able to do so without being forced to do so in a manner that matches performance needs. I think being happy not having any query planner at all is a bit extreme, but there are times when queries need to be tuned to force early data culling or adherence to indexes, I find these times to ultimately be pretty rare and generally have to review all the instances of our manual optimization whenever we update postgres to see which ones the query planner can now do better. This change would still allow you to sidestep the query planner, but would also open up expressive CTEs to use in performance sensitive queries.
- anarazel 8y ago
  I think "will be making" unfortunately includes a bit of wishful thinking. It's far from clear we'll have a patch that's mature enough by the time the feature freeze is there and that also has the necessary support.

jordanthoms 8y ago

Don't think I'd like having no query planner, most of the time it does the right thing. But having the ability to more easily correct it when it goes wrong would be nice (arguably postgres already supports the ideal way of doing this with it's custom statistics, though sometimes it's easier to have a more manual knob).

munk-a 8y ago

Sorry, to clarify the "(mandatory)" in the comment above, pg12 will be making CTEs optionally optimization fences, so people who want to hand-plan their query still can, but people who wish to use CTEs for organization and clarity will be able to do so without being forced to do so in a manner that matches performance needs. I think being happy not having any query planner at all is a bit extreme, but there are times when queries need to be tuned to force early data culling or adherence to indexes, I find these times to ultimately be pretty rare and generally have to review all the instances of our manual optimization whenever we update postgres to see which ones the query planner can now do better. This change would still allow you to sidestep the query planner, but would also open up expressive CTEs to use in performance sensitive queries.

anarazel 8y ago
I think "will be making" unfortunately includes a bit of wishful thinking. It's far from clear we'll have a patch that's mature enough by the time the feature freeze is there and that also has the necessary support.