Skip site navigation (1) Skip section navigation (2)

CTE optimization fence on the todo list?

From: Daniel Browning <db(at)kavod(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: CTE optimization fence on the todo list?
Date: 2012-09-19 20:05:44
Message-ID: 201209191305.44674.db@kavod.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2012-09-19 20:13:51
Subject: Re: [v9.3] Extra Daemons (Re: elegant and effective way for running jobs inside a database)
Previous:From: ktm@rice.eduDate: 2012-09-19 19:47:35
Subject: Re: Invalid optimization of VOLATILE function in WHERE clause?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group