Re: Transient plans versus the SPI API

On 2011-08-03 21:19, 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?
> Interesting thought, but I really don't see a way to make it practical.

See also http://archives.postgresql.org/pgsql-hackers/2010-11/msg00617.php

I don't know if any implementation can be practical - maybe the parser
could be coerced into emitting some kind of number that's based on
everything in the query, except constants (and whitespace), so it would
be the same for all the queries Robert described. That could be low cost
enough to detect of for a query's id a cached plan exists and do more
work only in those cases.

--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data