Re: Upgrading JDBC driver from 7.3 to 8.4 affected execution plans

On 04/10/10 11:35, Maciek Sakrejda wrote:
> Could this be related to how planning works with respect to
> parameterized queries [1]? If memory serves, a prepared statement is
> typically planned once and that plan is used each time the statement
> is executed. There can be some issues around this because when you
> have a query like "SELECT * FROM foo WHERE bar > ?", the planner has
> no idea of the selectivity of that predicate if it has to plan the
> query before it knows what the parameters will be. E.g., if it picks
> an index scan and then a query is submitted where ? is 10, if 95% of
> bars are greater than 10, the executor will waste a lot of time
> mucking around with the index where it should just be doing a seq
> scan.

IMO Pg would be well served by supporting something like "EXECUTE WITH
REPLAN" or a PREPARE that doesn't cache the plan, only storing the
parsed statement and handling parameter substitution.

This is in the TODO as "Allow finer control over the caching of prepared
query plans" http://wiki.postgresql.org/wiki/Todo . Perhaps this is
something a few commercial users of Pg might consider getting together
and sponsoring work on?

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/