Dave Cramer wrote:
> The only time prepared statements won't replan is if you re-use the
> prepared statement, which your code does not.
>
> However... AFAIK, postgresql does not plan based on the parameter.
OK, PG definitely plans based on the parameter value. I turned on plan
logging in the server log, and without specifying prepareThreshold at
all (e.g. setting it to 0), I can see that PG is making different plan
estimates for different parameter values. It might be using the same
general plan, but I can't bear to wade through all that output to tell.
Looking at ANALYZE output on the unadorned, non-prepared query, I can
see that different plans may be made, depending on the parameter. In a
table with millions of rows, there are tens of thousands of distinct
values of this parameter, with a very wide distribution. One value has
200K occurrences, many others just a handful. Using a different plan
for the outliers can be (and is) very helpful. When I wrap the query in
a SQL PREPARE statement, a generic plan is made that works OK for 99.9%
of the column values, but for the most common column value, that plan is
20,000 times slower than the plan that is chosen by the planner when it
knows that the query involves that most common value. Hence my interest
in what is going on behind prepareStatement(). I'm good now.
-Kevin Murphy
