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

Yes, we are aware of the change from the 5th to the 6th iteration. This is
not the problem. The 5th, and 6th iteration are pretty close, the problem
occurs further down

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 12 January 2016 at 07:15, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> 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
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>