From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Serge Rielau <serge@rielau.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Doug Doole <ddoole@salesforce.com>
Subject: Re: Cached plans and statement generalization
Date: Tue, 25 Apr 2017 19:45:20 +0300
Message-ID: <1f4c1a22-ecbe-811d-4695-ee4701b8f6c7@postgrespro.ru> (raw)
In-Reply-To: <CBF37923-CDA5-48EF-9E9E-4A338D7988ED@rielau.com>
References: <8e76d8fc-8b8c-14bd-d4d1-e9cf193a74f5@postgrespro.ru>
<20170424184353.u2krt2eiw2l6e2af@alap3.anarazel.de>
<f0bfcdd6-dba3-e9a8-2108-146e1f880839@postgrespro.ru>
<CBF37923-CDA5-48EF-9E9E-4A338D7988ED@rielau.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 25.04.2017 19:12, Serge Rielau wrote:
>
>> On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik
>> <k.knizhnik@postgrespro.ru <mailto: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
> Salesforce.com <http://salesforce.com;
>
> PS: FWIW, I like this feature.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: k.knizhnik@postgrespro.ru, serge@rielau.com, ddoole@salesforce.com
Subject: Re: Cached plans and statement generalization
In-Reply-To: <1f4c1a22-ecbe-811d-4695-ee4701b8f6c7@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox