>My guess is that the plan that is generated using the prepared statement only
works for some input values, but not for all (a problem that Oracle has suffered
from for ages as well).
I think the problem is as follows:
1) During initial runs in server-prepared mode PG still uses exact
values to plan the query
2) It turns out that for certain conditions PG properly understands
that certain conditions are bad.
I bet $1 = '180', $2 = '1' returns lots of rows, thus backend chooses
to start with $3 = '7728'.
3) Later backend creates generic plan. That one does not include
knowledge of exact $1 and &2 values. Thus backend estimates the
cardinality differently.
That results a plan flip.
Note: in Oracle (9-10) bind peeking worked in another way.
There, a plan built for the first bind values was reused for all the
subsequent executions.
That makes more sense for me since that gives at least some stability
(it avoids sudden plan flips).
I'll try to file a case to hackers list to check what they say on the plan flip.
PS. The sad thing is JDBC does have room for "SQL-injection-safe,
non-server-prepared query". In other words, plain java.sql.Statement
does not have "setXXX" methods, and PreparedStatement has no
user-provided API to convey "please, do not use server-prepared
statement as the plan is very data-dependent".
PPS. I do not think the plan is data-dependent in the particular case.
I bet the same plan works for all the values that particular
application uses. It is just PG's fault that plan flip happens.
Vladimir