Let try this again…
From: Kevin Fallis
Sent: Friday, December 01, 2006 1:04 PM
To: 'pgsql-interfaces@postgresql.org'; 'pgsql-performance@postgresql.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@tazznetworks.com
913.488.4705
