Re: Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

Vladimir Sitnikov wrote:
> >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.

I didn't look at the specific query, but I have seen cases like that before.

During the first 5 executions, PostgreSQL generates a specific plan
and remembers the *estimated* cost.
After that, the cost for the generic plan (with $1, $2, ...) is
estimated and compared to the average of the estimated costs of
the previous specific plans.
If the generic plan is no worse, it is used from then on.

The problem is that only estimates are compared.
The performance drop from the sixth execution on usually means that
the cost estimate for the generic plan is off.

It would be helpful to create a prepared statement, and on the sixth
execution capture the output of
EXPLAIN (ANALYZE, BUFFERS) EXECUTE mystmt(args);

Analyzing that should help you find out where things go wrong.

Yours,
Laurenz Albe