Re: first time hacker ;) messing with prepared statements

PFC wrote:
>> PFC wrote:
>>> Hello,
>>> So, I embarked (yesterday) on a weekend project to add a new
>>> feature to Postgres...
>>> I use PHP with persistent connections and always have been
>>> bothered that those very small AJAX queries (usually simple selects
>>> returning 1 row) take more CPU in postgres to parse & plan than to
>>> actually execute.
>> Microsoft's answer to this issue with SQLServer appears to have been
>> to introduce a smart
>> cache for all statement plans. It seems to be very effective. I guess
>> you're doing much the
>> same thing but with more user intervention, in effect.
> Actually, the main purpose was to 1) have fun hacking Postgres, and 2)
> perhaps something useful would come of it...
Nice job so far!
Since you have achieved (1), now its time to get to (2) and I thinks its
very well possible. In general its quite nice to have parts of your work
integrated.
> And I did find it very interesting, probably due to the fact that
> Postgres source code is so... impressively clean... well organized...
> readable... it took two hours from downloading the source to having an
> extra functional system catalog, and it worked at the first compile,
> all due to the concise but to the point comments in include/catalog, I
> couldn't believe it.
> Anyway, Microsoft's solution is cool, too, but you need to reparse
> the entire query to then detect "I've planned this query before, with
> other parameters, so I'll reuse that prepared plan", so it adds
> another parsing step, which is less efficient.
The MAJOR benefit of Microsoft's approach is that it works on existing
application, 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.

> Postgres could also do that with a small modification, by the way
> : like by using the entire string (with $1 style parameters) instead
> of the statement name, use that as a cache key, and send parameters
> separately, but I think it would be less clean than, say, a statement
> called "get_user_by_id" or something.
> Also I like the idea of named prepared queries, which feel a bit
> like procedures, because many of those small, often-used queries would
> end up being defined in the same place, which makes schema changes
> (and having to modify queries in your application) slightly less painful.
> PHP can also use pg_exec() which sends the parameters separately,
> automagically converted to postgres format, so you save time and
> hassle on quoting and de-quoting versus a SQL EXECUTE.
> Since the performance of pg_exec() is almost twice as fast as
> plain SQL, and PHP scripts tend to use quite a lot of these queries,
> it also means a free database server performance upgrade (ie. the same
> DB server can handle more webservers, for instance).
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.

I thinks a good middle ground to address the "persistence" problems I
think there are and keep close to your patch might be using "stored
procedures" and have these cached globally.

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.
> 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.

- Joris