Re: Transient plans versus the SPI API

On Wed, Aug 3, 2011 at 3:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.
>
> 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.

Fair enough. I have to admit I'm afraid of them right now.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company