Re: Is it useful to record whether plans are generic or custom?

út 17. 11. 2020 v 15:21 odesílatel torikoshia <torikoshia(at)oss(dot)nttdata(dot)com>
napsal:

> On 2020-11-12 14:23, Pavel Stehule wrote:
>
> > yes, the plan self is very interesting information - and information
> > if plan was generic or not is interesting too. It is other dimension
> > of query - maybe there can be rule - for any query store max 100 most
> > slows plans with all attributes. The next issue is fact so first first
> > 5 execution of generic plans are not generic in real. This fact should
> > be visible too.
>
> Thanks!
> However, AFAIU, we can know whether the plan type is generic or custom
> from the plan information as described in the manual.
>
> -- https://www.postgresql.org/docs/devel/sql-prepare.html
> > If a generic plan is in use, it will contain parameter symbols $n,
> > while a custom plan will have the supplied parameter values substituted
> > into it.
>
> If we can get the plan information, the case like 'first 5 execution
> of generic plans are not generic in real' does not happen, doesn't it?
>

yes

postgres=# create table foo(a int);
CREATE TABLE
postgres=# prepare x as select * from foo where a = $1;
PREPARE
postgres=# explain execute x(10);
┌─────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..41.88 rows=13 width=4) │
│ Filter: (a = 10) │
└─────────────────────────────────────────────────────┘
(2 rows)

postgres=# explain execute x(10);
┌─────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..41.88 rows=13 width=4) │
│ Filter: (a = 10) │
└─────────────────────────────────────────────────────┘
(2 rows)

postgres=# explain execute x(10);
┌─────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..41.88 rows=13 width=4) │
│ Filter: (a = 10) │
└─────────────────────────────────────────────────────┘
(2 rows)

postgres=# explain execute x(10);
┌─────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..41.88 rows=13 width=4) │
│ Filter: (a = 10) │
└─────────────────────────────────────────────────────┘
(2 rows)

postgres=# explain execute x(10);
┌─────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..41.88 rows=13 width=4) │
│ Filter: (a = 10) │
└─────────────────────────────────────────────────────┘
(2 rows)

postgres=# explain execute x(10);
┌─────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..41.88 rows=13 width=4) │
│ Filter: (a = $1) │
└─────────────────────────────────────────────────────┘
(2 rows)

postgres=# explain execute x(10);
┌─────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════╡
│ Seq Scan on foo (cost=0.00..41.88 rows=13 width=4) │
│ Filter: (a = $1) │
└─────────────────────────────────────────────────────┘
(2 rows)

>
> Regards,
>
> --
> Atsushi Torikoshi
>