query optimization on prepared statement through connection vi libpq

["Kevin Fallis" <kfallis(at)tazznetworks(dot)com>]

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