User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Mon, 04 Dec 2006 10:25:12 -0700
Subject: Re: query optimization on prepared statement through
From: Keary Suska <hierophant@pcisys.net>
To: Postgres-Interfaces <pgsql-interfaces@postgresql.org>
Message-ID: <C199A808.919C%hierophant@pcisys.net>
Thread-Topic: [INTERFACES] query optimization on prepared statement
	through connection vi libpq
Thread-Index: AccVcxej/SgUhzFRTt+yOjP6QNm9eACVg+9J
In-Reply-To: 
 <07FDEE0ED7455A48AC42AC2070EDFF7C014F7A89@corpsrv2.tazznetworks.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit

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"