Re: first time hacker ;) messing with prepared statements - Mailing list pgsql-hackers

> The MAJOR benefit of Microsoft's approach is that it works on existing  
> application,
Yes, that is a nice benefit !Is there a way to turn it on/off ? Or is it smart enough to only cache  
plans for cases where it is relevant ?For instance, I absolutely want some queries to be planned according to  
real parameters (makes huge difference on some search queries, as  
expected), whereas most simple queries like the proverbial select by ID  
etc could be cached without problems...

> and, most importantly makes NO assumptions on the "volatile" server  
> state. A few cases where the Microsoft solution works, while yours will  
> fail is:
>
>     * Server restart and assorted like failover (you need to redo a
>       global prepare).
>     * Cleanup and instantiation of a prepared statement.
Hehe, actually, mine does work after restart since the statements are  
stored in a database-specific system catalog which is persistent.Actually, what I store is not the result of PREPARE (a
plan)but the text  
 
of the SQL query "PREPARE foo.....", that is I just cut the GLOBAL from  
"GLOBAL PREPARE" and store the rest. The actual PREPARE is realized by  
each connection when it encounters an EXECUTE request and doesn't find the  
cached plan. It is actually extremely simple ;) did you expect a fancy  
shared memory cache (ahem...) ? No, no, it's very basic.This way, if a table was dropped and recreated, or whatever
otherstuff  
 
that can invalidate a plan since the GLOBAL PREPARE was issued, no  
problem, since there was no global stored plan anyway, just some SQL text.  
Also if a needed table was dropped, the user will get the same error  
message as he would have got issuing a PREPARE for the associated SQL  
query string.The overhead of each connection doing its own PREPARE is negligible,  
since, if you use that feature, you intend to issue this query many, many  
times during the life of the persistent connection.

> What you are doing for a global query cache is already in consideration  
> and having plan invalidation mechanism on schema changes or, maybe,  
> statistic updates was a step into that direction. You code mostly  
> contributed the other parts already.
As I said it is much simpler than that : I store no plans ;)Of course this means it only works with persistent
connections.

> Another considerations is whether most task are getting CPU bound or IO  
> bound. A better, per query, plan might reduce IO load due to better use  
> of statistics on that single case, while for CPU bound it is very nice  
> to reduce the planning overhead significantly.
Well, if it is IO bound, then this thing is useless. However, since the  
purpose is to optimize often-used, simple queries, the likes of which  
abound in web applications, then it is relevant... because, if this kind  
of simple selects become IO bound, and you have a few on each page, you're  
really in trouble...

>> Another possible implementation would be to use a connection pooler  
>> which, when opening a new connection, can be configured to send a SQL  
>> script containing all the PREPARE statements.
> This is, IMHO, an application side feature that might be a good addition  
> to PHP and other languages that provide the "persistent connection"  
> feature.
On second thought, if it is not in Postgres, I'd rather put this in the  
connection pooler, because this way it can be used by different  
applications. But then you have to use a connection pooler. Also, by  
putting it in Postgres, statements are only prepared as needed, whereas  
the pooler would have to issue a lot of PREPAREs at connection startup,  
making new connection startup slower.