Some prepare / execute questions

Hi list,

I'm trying to figure out how this kind of query can be prepared:
SELECT * FROM forums WHERE categoryID IN (1, 3, 5, 6)

where the in-list is variable.


When trying to prepare something Like this:
PREPARE the_query (integer) AS SELECT * FROM something WHERE field=$1;
One gets an error like:
ERROR:  Unable to identify an operator '=$' for types 'integer' and
'integer'
         You will have to retype this query using an explicit cast

Of coarse, rewriting it to 'field = $1' solves that, but I wanted to
point it out anyway.

Another thing, I noticed one patch to have prepared statements kept
alive for multiple connections, will that ever come into the main-source
of postgresql?
I can't really imagine any reason not to. The argument "but the
structure might change over time outdating the plan or making its plan
very inferior" is not that good, imho.
There are, as I see it, not that many 'reasons' why the planner would
change its planning.

- Dropping of tables -> simply try to reprepare the statement, if it
fails display an error during the drop of the table or when someone
tries to execute the statement.

- Massive inserts/updates/deletes on tables -> when you don't
vacuum/analyze, the plans won't be updated for normal queries either
(afaik), so see vacuum.

- Vacuum/analyze -> Simply reprepare the statement to get an up-to-date
prepared statement again.

- Dropping or creating indexes -> the same as for vacuum.

And last, but not least.
When running some (prepared or not) query multiple times on the same
connection you see a huge speedup the second time:
first time: User selecter 1 rows fetched 17.560 ms (0.017560005188 s)
second time: User selecter 1 rows fetched 5.466 ms (0.00546598434448 s)

Of coarse, the speed up is great... Except that our forumsoftware never
runs the same queries twice, nor similar queries later on, since that
would have meant it issued to much queries and didn't store the results
that well. (simple answer, I know, and not entirely true but in most
websoftware like forums it is generally speaking: less queries is
better, unless a query would become to complex or would generate much
more results than otherwise).
It would be even better when postgresql was able to use those
query-plan-caches (?) over multiple connections as well.

As for the "why don't you use pconnects then?" questions:
We run multiple webservers (apache+php), every one of them able to
handle at most 255 apache-childs...
Those numbers _are_ met so now and then, on low load moments the servers
have already 80 (orso) clients dangling around.
With 5 webservers that means having 400 connections open to your
database, while only very few of them are actually used... We don't
really like that behaviour especially when load goes up, in that case
you could end up having over a thousand connections on a moment the
database machine is already heavily used.

Connectionpooling would, of coarse, be great but that isn't my point I'm
trying to make :)

For now we are using mysql, but are investigating other solutions, since
mysql is not the best database in terms of referential integrety and
query planning performance. That is excactly why I like (idea of) the
prepare statement of postgresql so much, it solves the issue of having
more overhead due to more advanced queryplanning, while the queries
_are_ better planned and might be faster than mysql's plans...

I might have to point out that mysql (with myisam tables) did the same
queries for one of the most used pages of our site in around 17ms (ie in
that time php was finished fetching all the results for all the
necessary statements) while postgresql initially takes around 86ms for
that... The second iteration on the same connection it is 45 vs 16.
And at the moment I'm investigating the prepare-statement improvements
(looks promising).
Both on the same machine, both "litely" optimised, the machine having no
load at all and doing only one request at a time and allowing it to
cache anything the databases would like.

Regards,

Arjen