Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tfco6-0003No-97 for pgsql-performance@arkaria.postgresql.org; Mon, 03 Dec 2012 20:40:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Tfco5-00063v-Lh for pgsql-performance@arkaria.postgresql.org; Mon, 03 Dec 2012 20:40:01 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdXU5-000630-Ip for pgsql-performance@postgresql.org; Wed, 28 Nov 2012 02:34:45 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdXU2-0001ys-GJ for pgsql-performance@postgresql.org; Wed, 28 Nov 2012 02:34:44 +0000 Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz [60.234.150.59]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id qAS2YCtS017162 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Wed, 28 Nov 2012 15:34:13 +1300 Message-ID: <50B57824.9040604@archidevsys.co.nz> Date: Wed, 28 Nov 2012 15:34:12 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/17.0 Thunderbird/17.0 MIME-Version: 1.0 To: Craig Ringer CC: Scott Marlowe , cedric@2ndquadrant.com, pgsql-performance@postgresql.org, Craig James , Kevin Grittner , Tom Lane , Claudio Freire , Merlin Moncure , David Greco Subject: Re: Hints (was Poor performance using CTE) References: <20121121134232.68590@gmx.com> <201211231105.56380.cedric@2ndquadrant.com> <50B57440.1040802@2ndquadrant.com> In-Reply-To: <50B57440.1040802@2ndquadrant.com> Content-Type: multipart/alternative; boundary="------------050606040501090406080302" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------050606040501090406080302 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 28/11/12 15:17, Craig Ringer wrote: > On 27/11/2012 3:42 PM, Scott Marlowe wrote: > >> Here here! PostgreSQL is well known for its extensibility and this is >> the perfect place for hints. > I agree with the sentiment and your concerns. However, this doesn't > solve the CTE problem. > > Some people are relying on the planner's inability to push conditions > into / pull conditions out of CTEs, and otherwise re-arrange them. If > support for optimising into eligible CTEs (ie CTE terms that contain > only SELECT or VALUES and call no VOLATILE functions) then these > applications will potentially encounter serious performance regressions. > > Should this feature never be added to Pg, making it different and > incompatible with other DBs that implement CTE optimisation, just > because some people are using it for a hacky hint like OFFSET 0? > > Should these applications just be broken by the update, with people > told to add `OFFSET 0` or load some not-yet-existing hints module > after reporting the performance issue to the list? > > I don't think either of those are acceptable. Sooner or later > somebody's going to want to add CTE optimisation, and I don't think > that "you can't" or "great, we'll do it and break everything" are > acceptable responses to any proposed patch someone might come up with > to add that. > > A GUC might be OK, as apps can always SET it before problem queries or > not-yet-ported code. It'd probably reduce the rate at which people > fixed their code considerably, though, going by past experience with > standard_conforming_strings, etc, but it'd work. > > -- > Craig Ringer > > I think it would be best to be something in the SQL for SELECT, as: 1. One is more likely to find it by looking up the documentation for SELECT 2. It could allow selective application within a SELECT: one could have several queries within the WITH clause: where all except one might benefit for optimisation, and the exception might cause problems I have suggested a couple possible syntax paterns, but there may well be better alternative syntaxes. Cheers, Gavin --------------050606040501090406080302 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 28/11/12 15:17, Craig Ringer wrote:
On 27/11/2012 3:42 PM, Scott Marlowe wrote:

Here here!  PostgreSQL is well known for its extensibility and this is
the perfect place for hints.
I agree with the sentiment and your concerns. However, this doesn't solve the CTE problem.

Some people are relying on the planner's inability to push conditions into / pull conditions out of CTEs, and otherwise re-arrange them. If support for optimising into eligible CTEs (ie CTE terms that contain only SELECT or VALUES and call no VOLATILE functions) then these applications will potentially encounter serious performance regressions.

Should this feature never be added to Pg, making it different and incompatible with other DBs that implement CTE optimisation, just because some people are using it for a hacky hint like OFFSET 0?

Should these applications just be broken by the update, with people told to add `OFFSET 0` or load some not-yet-existing hints module after reporting the performance issue to the list?

I don't think either of those are acceptable. Sooner or later somebody's going to want to add CTE optimisation, and I don't think that "you can't" or "great, we'll do it and break everything" are acceptable responses to any proposed patch someone might come up with to add that.

A GUC might be OK, as apps can always SET it before problem queries or not-yet-ported code. It'd probably reduce the rate at which people fixed their code considerably, though, going by past experience with standard_conforming_strings, etc, but it'd work.

--
Craig Ringer


I think it would be best to be something in the SQL for SELECT, as:
  1. One is more likely to find it by looking up the documentation for SELECT

  2. It could allow selective application within a SELECT: one could have several queries within the WITH clause: where all except one might benefit for optimisation, and the exception might cause problems
I have suggested a couple possible syntax paterns, but there may well be better alternative syntaxes.


Cheers,
Gavin
--------------050606040501090406080302--