Re: Is it useful to record whether plans are generic or custom? - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Is it useful to record whether plans are generic or custom? |
| Date | |
| Msg-id | CAFj8pRCmY5DdpODpPvxa2Nvye+mKiq43x4AtkYGgAb3J1Y-LoQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Is it useful to record whether plans are generic or custom? (torikoshia <torikoshia@oss.nttdata.com>) |
| Responses |
Re: Is it useful to record whether plans are generic or custom?
|
| List | pgsql-hackers |
út 17. 11. 2020 v 15:21 odesílatel torikoshia <torikoshia@oss.nttdata.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)
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
pgsql-hackers by date: