On 25.04.2017 19:12, Serge Rielau 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