Обсуждение: query optimization on prepared statement through connection vi libpq

<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>

on 12/1/06 11:04 AM, kfallis@tazznetworks.com purportedly said:

> 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.

AFAIK, the query plan for a prepared statement never changes, so if the
"original" plan uses sequential scans, subsequent calls to the prepared
statement will use a sequential scan even if it's not optimal at a later
point. 

> 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?

you will likely have to do something along these lines, but keep in mind
that prepared statements are per-connection, and you can't replace an
existing defined prepared statement.

I am willing to bet that VACUUM doesn't touch any prepared query plans--it
probably doesn't have a way of knowing what/where they are.

Note that even if queries use an updated query plan, the planner may not
choose the best plan anyway. There are various parameters that may effect
the planner, such as inferred types and joins across different (although
compatible) column types. This last bit may have been solved recently,
however.

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

On Tue, December 5, 2006 00:25, Keary Suska wrote:

> Note that even if queries use an updated query plan, the planner may not
> choose the best plan anyway. There are various parameters that may effect
> the planner, such as inferred types and joins across different (although
> compatible) column types. This last bit may have been solved recently,
> however.

Also, IIRC it's possible to create indexes on function results.  So if you
have, say, an integer column X then you could possibly index that table on
"X converted to text."


Jeroen