Skip site navigation (1) Skip section navigation (2)

Re: Transient plans versus the SPI API

From: Yeb Havinga <yebhavinga(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Transient plans versus the SPI API
Date: 2011-08-04 09:12:24
Message-ID: 4E3A6278.3050404@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2011-08-04 09:12:55
Subject: Re: Postgres / plpgsql equivalent to python's getattr() ?
Previous:From: Boszormenyi ZoltanDate: 2011-08-04 09:10:01
Subject: Re: SYNONYMS (again)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group