query optimization on prepared statement through connection vi libpq - Mailing list pgsql-interfaces

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Background:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have a connection pool around the libpq connection framework that I have set up that has a working
knowledgeof SQL statements that have been prepared.  It tracks if statements have been prepared or not and if they
haven’tit 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
assumingthat at some point in time, if an ANALYZE or VACUUM ANALYZE is performed, all connections in my connection pool
wouldre-optimize the plans so I get the best usage of indexes for the size of the entries in any table I am connected
with.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Problem:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">It appears that my connections (there could be many with prepared statements associated with them)
arenot falling to indexes that should otherwise be hit in a heavy load, high record count activity against a table.  I
cansee that sequential scans are rampant when in fact these selects should be indexed.  I have VACUUMED, swept and
windexedthe hell out of the tables and still I am seeing sequential scans.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Is there any reason I should consider a threading mechanism that re-prepares statements to reform
thequery plan info?  And..should not the ANALYZE adjust existing prepared queries for me?</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Kevin Fallis</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><a href="mailto:kfallis@tazznetworks.com">kfallis@tazznetworks.com</a></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">913.488.4705</span></font></div>