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
