From: Keary Suska <hierophant@pcisys.net>
To: Postgres-Interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: query optimization on prepared statement through
Date: Mon, 04 Dec 2006 10:25:12 -0700
Message-ID: <C199A808.919C%hierophant@pcisys.net> (raw)
In-Reply-To: <07FDEE0ED7455A48AC42AC2070EDFF7C014F7A89@corpsrv2.tazznetworks.com>
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"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: hierophant@pcisys.net
Subject: Re: query optimization on prepared statement through
In-Reply-To: <C199A808.919C%hierophant@pcisys.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox