FW: query optimization on prepared statement through connection vi libpq

Let try this again...

________________________________

From: Kevin Fallis
Sent: Friday, December 01, 2006 1:04 PM
To: 'pgsql-interfaces(at)postgresql(dot)org';
'pgsql-performance(at)postgresql(dot)org'
Subject: query optimization on prepared statement through connection vi
libpq

Background:

I have a connection pool around the libpq connection framework that I
have set up that has a working knowledge of SQL statements that have
been prepared. It tracks if statements have been prepared or not and if
they haven't it will prepare them so I can optimize statements going
through that connection. When the statement is prepared, I am assuming
the query planner does all the magic to figure out indexes to use and
whatnot. I am also assuming that at some point in time, if an ANALYZE
or VACUUM ANALYZE is performed, all connections in my connection pool
would re-optimize the plans so I get the best usage of indexes for the
size of the entries in any table I am connected with.

Problem:

It appears that my connections (there could be many with prepared
statements associated with them) are not falling to indexes that should
otherwise be hit in a heavy load, high record count activity against a
table. I can see that sequential scans are rampant when in fact these
selects should be indexed. I have VACUUMED, swept and windexed the hell
out of the tables and still I am seeing sequential scans.

Is there any reason I should consider a threading mechanism that
re-prepares statements to reform the query plan info? And..should not
the ANALYZE adjust existing prepared queries for me?

Kevin Fallis

kfallis(at)tazznetworks(dot)com

913.488.4705