I would like to have the option of disabling the CTE optimization fence for certain CTEs and/or queries. Can that be added to the official todo list? If not, why not?
I would find the option beneficial because large, complicated queries are often a lot clearer, simpler, and easier to read with CTEs than the equivalent query without CTEs. In some cases, the query with CTEs is also faster because of the optimization fence. But in other cases, the fence makes it a lot slower. In the latter cases, you are left with a choice between ugly and slow. If there was some method to disable the optimization fence for certain CTEs or entire queries, then it would be possible to have the best of both worlds. I apologize if this has already been covered before. I could only find two earlier discussions on this topic: http://archives.postgresql.org/pgsql-performance/2011-10/msg00208.php http://archives.postgresql.org/pgsql-performance/2011-11/msg00015.php In the latter, I counted four people would are in support of the general idea: Robert Haas, Andres Freund, Gavin Flower, Justin Pitts. However, I'm sure there are a lot of conflicting ideas on how exactly to go about it, such as whether to enable or disable it by default, the specific syntax to use, backwards compatibility, future-proofing, etc. One good reason to reject it would be if it can't be done with SQL standard syntax and would require some sort of PG-specific hint or GUC variable for the query planner. If so, then I understand that it's opposed for all the same reasons that hints are opposed in general. Another good reason to reject it might be because the only way to disable the CTE fence is to disable it by default. If that were the case, then I would imagine that it would break backwards compatibility, especially in the case of writable CTEs that currently depend on the fence for their current functionality. If there is no way to palatably enable it by default but allow certain CTEs or certain queries to disable it, then I don't see any way around that problem. A third reason I can imagine is that the only desirable solution (e.g. the one without additional non-standard keywords or session GUC variables) is effectively impossible. For example, if it requires that the query planner determine definitively whether a CTE is read only or not, that may be a bridge too far. A fourth possible reason is that the core team feels that CTEs do not improve readability, or that any such readability benefits are not worth the effort to support the option. Personally, I feel that the queries which could most benefit from the readability of CTEs are precisely the same ones that could most often benefit from the performance increase of disabling the fence (particularly if it could be done on a per-CTE basis rather than for the whole query at once). Of course the real reason could be something else entirely, hence this post. Thanks in advance for your feedback. -- DB -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers