Re: Transient plans versus the SPI API

On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > This seems like a good design. Now what would be really cool is if
> > you could observe a stream of queries like this:
>
> > SELECT a, b FROM foo WHERE c = 123
> > SELECT a, b FROM foo WHERE c = 97
> > SELECT a, b FROM foo WHERE c = 236
>
> > ...and say, hey, I could just make a generic plan and use it every
> > time I see one of these. It's not too clear to me how you'd make
> > recognition of such queries cheap enough to be practical, but maybe
> > someone will think of a way...
>
> Hm, you mean reverse-engineering the parameterization of the query?

Yes, basically re-generate the query after (or while) parsing, replacing
constants and arguments with another set of generated arguments and
printing the list of these arguments at the end. It may be easiest to do
This in parallel with parsing.

> Interesting thought, but I really don't see a way to make it practical.

Another place where this could be really useful is logging & monitoring

If there were an option to log the above queries as

"SELECT a, b FROM foo WHERE c = $1", (123)
"SELECT a, b FROM foo WHERE c = $1", (97)
"SELECT a, b FROM foo WHERE c = $1", (236)

it would make all kinds of general performance monitoring tasks also
much easier, not to mention that this forw would actually be something
that kan be cached internally.

For some users this might even be worth to use this feature alone,
without it providing Repeating Plan Recognition.

> In any case, it would amount to making up for a bad decision on the
> application side, ie, not transmitting the query in the parameterized
> form that presumably exists somewhere in the application. I think
> we'd be better served all around by encouraging app developers to rely
> more heavily on parameterized queries ... but first we have to fix the
> performance risks there.
>
> regards, tom lane
>