Re: Prepared statements and generic plans

On Tue, Jun 14, 2016 at 08:37:12AM +0000, Albe Laurenz wrote:
> Bruce Momjian wrote:
> > However, for the wire protocol prepare/execute, how do you do EXPLAIN?
> > The only way I can see doing it is to put the EXPLAIN in the prepare
> > query, but I wasn't sure that works. So, I just wrote and tested the
> > attached C program and it properly output the explain information, e.g.
> >
> > res = PQprepare(conn, "prep1", "EXPLAIN SELECT * FROM pg_language", 0, NULL);
> > -------
> > generated:
> >
> > QUERY PLAN
> >
> > Seq Scan on pg_language (cost=0.00..1.04 rows=4 width=114)
> >
> > so that works --- good.
>
> Hm, yes.
>
> Were you just curious or is it relevant for the documentation update?

I was curious because if there was no way to do it, I should document
that.

> >>> Looking at how the code behaves, it seems custom plans that are _more_
> >>> expensive (plus planning cost) than the generic plan switch to the
> >>> generic plan after five executions, as now documented. Custom plans
> >>> that are significantly _cheaper_ than the generic plan _never_ use the
> >>> generic plan.
> >>
> >> Yes, that's what the suggested documentation improvement says as well,
> >> right?
> >
> > Yes. What is odd is that it isn't the plan of the actual supplied
> > parameters that is cheaper, just the generic plan that assumes each
> > distinct value in the query is equally likely to be used. So, when we
> > say the generic plan is cheaper, it is just comparing the custom plan
> > with the supplied parameters vs. the generic plan --- it is not saying
> > that running the supplied constants with the generic plan will execute
> > faster, because in fact we might be using a sub-optimial generic plan.
>
> Right, that's why it is important to document that it is estimates that are
> compared, not actual costs.
>
> This has caused confussion in the past, see
> https://www.postgresql.org/message-id/flat/561E749D(dot)4090301%40socialserve(dot)com#561E749D(dot)4090301(at)socialserve(dot)com
>
> > Right. Updated patch attached.
>
> I am happy with the patch as it is.

Good.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +