Re: Cached plans and statement generalization

On 25.04.2017 19:12, Serge Rielau wrote:
>
>> On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik
>> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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