digoal
2023-03-27
PostgreSQL , PolarDB , GENERIC_PLAN , 变量
generic plan: 可以理解为默认(根据数据柱状分布, 偏大众的通用)执行计划. 当使用变量作为SQL条件时, 如果你是优化器, 你肯定假设输入的是概率较大的值, 按这个逻辑来生成执行计划.
例如一个表有1000万记录, 其中某个值占了99%, 剩下的是其他值, 如果where这个字段等于某个值, 你会使用什么执行计划呢?
《如何explain查看prepared statement的generic plan》
《PostgreSQL 14 preview - 统计 generic/custom plan in pg_stat_statements - 硬解析、软解析统计》
《PostgreSQL 14 支持绑定变量语句(prepared statements)的 custom_plans 与 generic_plans 次数统计》
Invent GENERIC_PLAN option for EXPLAIN.
author Tom Lane <tgl@sss.pgh.pa.us>
Fri, 24 Mar 2023 21:07:14 +0000 (17:07 -0400)
committer Tom Lane <tgl@sss.pgh.pa.us>
Fri, 24 Mar 2023 21:07:22 +0000 (17:07 -0400)
commit 3c05284d83b230728e59a25e828992037ef77096
tree 4e8ef1779b279c01ac28413f1ea7fccbcac9ed7f tree
parent 5b140dc8f094e8a11fccab6bdee50d4c599e7444 commit | diff
Invent GENERIC_PLAN option for EXPLAIN.
This provides a very simple way to see the generic plan for a
parameterized query. Without this, it's necessary to define
a prepared statement and temporarily change plan_cache_mode,
which is a bit tedious.
One thing that's a bit of a hack perhaps is that we disable
execution-time partition pruning when the GENERIC_PLAN option
is given. That's because the pruning code may attempt to
fetch the value of one of the parameters, which would fail.
Laurenz Albe, reviewed by Julien Rouhaud, Christoph Berg,
Michel Pelletier, Jim Jones, and myself
Discussion: https://postgr.es/m/0a29b954b10b57f0d135fe12aa0909bd41883eb0.camel@cybertec.at
+-- GENERIC_PLAN option
+select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1');
+ explain_filter
+---------------------------------------------------------------------------------
+ Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N width=N)
+ Recheck Cond: (thousand = $N)
+ -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N)
+ Index Cond: (thousand = $N)
+(4 rows)
+
+-- should fail
+select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');
+ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together
+CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement
+-- Test EXPLAIN (GENERIC_PLAN) with partition pruning
+-- partitions should be pruned at plan time, based on constants,
+-- but there should be no pruning based on parameter placeholders
+create table gen_part (
+ key1 integer not null,
+ key2 integer not null
+) partition by list (key1);
+create table gen_part_1
+ partition of gen_part for values in (1)
+ partition by range (key2);
+create table gen_part_1_1
+ partition of gen_part_1 for values from (1) to (2);
+create table gen_part_1_2
+ partition of gen_part_1 for values from (2) to (3);
+create table gen_part_2
+ partition of gen_part for values in (2);
+-- should scan gen_part_1_1 and gen_part_1_2, but not gen_part_2
+select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1');
+ explain_filter
+---------------------------------------------------------------------------
+ Append (cost=N.N..N.N rows=N width=N)
+ -> Seq Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N width=N)
+ Filter: ((key1 = N) AND (key2 = $N))
+ -> Seq Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N width=N)
+ Filter: ((key1 = N) AND (key2 = $N))
+(5 rows)
+
+drop table gen_part;
+ <varlistentry>
+ <term><literal>GENERIC_PLAN</literal></term>
+ <listitem>
+ <para>
+ Allow the statement to contain parameter placeholders like
+ <literal>$1</literal>, and generate a generic plan that does not
+ depend on the values of those parameters.
+ See <link linkend="sql-prepare"><command>PREPARE</command></link>
+ for details about generic plans and the types of statement that
+ support parameters.
+ This parameter cannot be used together with <literal>ANALYZE</literal>.
+ It defaults to <literal>FALSE</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
