>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