út 17. 11. 2020 v 15:21 odesílatel torikoshia <torikos...@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) > > Regards, > > -- > Atsushi Torikoshi >
