Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d33cT-0002r2-Bo for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Apr 2017 16:47:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d33cS-0006Xt-Ux for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Apr 2017 16:47:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d33au-0001LZ-Ir for pgsql-hackers@postgresql.org; Tue, 25 Apr 2017 16:45:40 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d33aT-0001Wa-NA for pgsql-hackers@postgresql.org; Tue, 25 Apr 2017 16:45:40 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id C4EB521C294D; Tue, 25 Apr 2017 19:45:12 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru Subject: Re: Cached plans and statement generalization DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1493138712; bh=LJAgUso5KebIQuPpULebONxEBspSnrWTIIVMleu7w40=; h=To:References:Cc:From:Date:In-Reply-To; b=PScWCi1XcAVuvsBwKwEezY7DADicRswEfJJyUTIm6kjD2+eeqtA1s37c7JOMBsjGb 997XxyV8MyDY0pFSG72FHjCjEnATECw0zRMIMKdYXFj3xd+K+nayb3SBOkwmq+902O /yyI6FfHvP/TrBe300r8pdx+DLMlkTFKBUeB6fFs= To: Serge Rielau References: <8e76d8fc-8b8c-14bd-d4d1-e9cf193a74f5@postgrespro.ru> <20170424184353.u2krt2eiw2l6e2af@alap3.anarazel.de> Cc: Pg Hackers , Doug Doole From: Konstantin Knizhnik Message-ID: <1f4c1a22-ecbe-811d-4695-ee4701b8f6c7@postgrespro.ru> Date: Tue, 25 Apr 2017 19:45:20 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------705E06F574F97599605F386D" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------705E06F574F97599605F386D Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit On 25.04.2017 19:12, Serge Rielau wrote: > >> On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik >> > wrote: >> Another problem is caused by using integer literals in context where >> parameters can not be used, for example "order by 1”. > You will also need to deal with modifiers in types such as > VARCHAR(10). Not sure if there are specific functions which can only > deal with literals (?) as well. Sorry, I do not completely understand how presence of type modifiers can affect string literals used in query. Can you provide me some example? > > Doug Doole did this work in DB2 LUW and he may be able to point to > more places to watch out for semantically. > > Generally, in my experience, this feature is very valuable when > dealing with (poorly designed) web apps that just glue together strings. I do not think that this optimization will be useful only for poorly designed application. I already pointed on two use cases where prepapred statements can not be used: 1. pgbouncer without session-level pooling. 2. partitioning > Protecting it under a GUC would allow to only do the work if it’s > deemed likely to help. > Another rule I find useful is to abort any efforts to substitute > literals if any bind variable is found in the query. > That can be used as a cue that the author of the SQL left the > remaining literals in on purpose. > > A follow up feature would be to formalize different flavors of peeking. > I.e. can you produce a generic plan, but still recruit the initial set > of bind values/substituted literals to dos costing? Here situation is the same as for explicitly prepared statements, isn't it? Sometimes it is preferrable to use specialized plan rather than generic plan. I am not sure if postgres now is able to do it. > > Cheers > Serge Rielau > Salesforce.com > > PS: FWIW, I like this feature. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------705E06F574F97599605F386D Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

On 25.04.2017 19:12, Serge Rielau wrote:

On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:
Another problem is caused by using integer literals in context where parameters can not be used, for example "order by 1”.
You will also need to deal with modifiers in types such as VARCHAR(10). Not sure if there are specific functions which can only deal with literals (?) as well.

Sorry, I do not completely understand how presence of type modifiers can affect string literals used in query.
Can you provide me some example?


Doug Doole did this work in DB2 LUW and he may be able to point to more places to watch out for semantically.

Generally, in my experience, this feature is very valuable when dealing with (poorly designed) web apps that just glue together strings.

I do not think that this optimization will be useful only for poorly designed application.
I already pointed on two use cases where prepapred statements can not be used:
1. pgbouncer without session-level pooling.
2. partitioning

Protecting it under a GUC would allow to only do the work if it’s deemed likely to help.
Another rule I find useful is to abort any efforts to substitute literals if any bind variable is found in the query.
That can be used as a cue that the author of the SQL left the remaining literals in on purpose.

A follow up feature would be to formalize different flavors of peeking. 
I.e. can you produce a generic plan, but still recruit the initial set of bind values/substituted literals to dos costing?
Here situation is the same as for explicitly prepared statements, isn't it?
Sometimes it is preferrable to use specialized plan rather than generic plan.
I am not sure if postgres now is able to do it.



Cheers
Serge Rielau

PS: FWIW, I like this feature.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--------------705E06F574F97599605F386D--