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...
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.
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).
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.
Are you sure that you application wouldn't benefit more from a MOM
solution with
persisted database connections? Have you looked at
http://safmq.sourceforge.net/?
Dunno. Is this related to Postgres ?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
