Re: query optimization on prepared statement through

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

on 12/1/06 11:04 AM, kfallis(at)tazznetworks(dot)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