PostgreSQL PREPARE语句

PREPARE 为执行准备一个语句

PREPARE name [ ( data_type [, ...] ) ] AS statement

• name： 给这个特定预备语句的任意名称。它在一个会话中必须唯一并且后续将被用来执行或者清除一个之前准备好的语句。

• data_type：预备语句一个参数的数据类型。如果一个特定参数的数据类型没有被指定或者被指定为unknown，将从该参数被使用的环境中推得。要在预备语句本身中引用参数，可以使用 2等。

• statement：任何SELECT、INSERT、UPDATE、DELETE或者VALUES语句。

PREPARE创建一个预备语句。预备语句是一种服务器端对象，它可以被用来优化性能。当PREPARE语句被执行时，指定的语句会被解析、分析并且重写。当后续发出一个EXECUTE命令时，该预备语句会被规划并且执行。这种工作的划分避免了重复性的解析分析工作，不过允许执行计划依赖所提供的特定参数值。

预备语句可以接受参数：在执行时会被替换到语句中的值。在创建预备语句时，可以用位置引用参数，如2等。也可以选择性地指定参数数据类型的一个列表。当一个参数的数据类型没有被指定或者被声明为unknown时，其类型会从该参数被使用的环境中推知（如果可能）。在执行该语句时，在EXECUTE语句中为这些参数指定实际值。

预备语句只在当前数据库会话期间存在。当会话结束时，预备语句会消失，因此在重新使用之前必须重新建立它。这也意味着一个预备语句不能被多个数据库客户端同时使用。不过，每一个客户端可以创建它们自己的预备语句来使用。预备语句可以用DEALLOCATE命令手工清除。

当一个会话要执行大量类似语句时，预备语句可能会有最大性能优势。如果该语句很复杂（难于规划或重写），例如，如果查询涉及很多表的连接或者要求应用多个规则，性能差异将会特别明显。如果语句相对比较容易规划和重写，但是执行起来开销相对较大，那么预备语句的性能优势就不那么显著了。

对每一组提供的EXECUTE值，预备语句可以使用通用计划而不是重新做计划。对于没有参数的预备语句马上就会这样做，否则只有五次或者更多次执行产生的计划的估计代价平均值（包括规划开销）比通用计划的代价估计更昂贵时才会这样做。一旦选中一个通用计划，在该预备语句剩余的生存时间内都将使用它。使用在重复值很多的列中很少出现的EXECUTE值可以产生比通用计划更加廉价的定制计划（即使加上规划开销），这样通用计划将不会被使用。

通用计划假定每一个提供给EXECUTE的值都是该列的可区分值并且列值是均匀分布的。例如，如果统计信息记录了三个可区分的列值，通用计划会假定一个列等值比较将匹配被处理行中的 33%。列统计值也允许通用计划准确地计算唯一列的选择度。如果一个通用计划被选中时，在非均匀分布列上的比较以及制定不存在的值都会影响平均计划代价。

要检查一个预备语句使用的查询计划，可以使用EXPLAIN，例如EXPLAIN EXECUTE。如果使用的是一个通用计划，它将包含参数符号$n，而一个定制计划则会把提供的参数值替换进去。通用计划中的行估计值反映了这些参数计算出来的选择度。

更多关于查询规划的统计信息的内容，下文中的ANALYZE部分。

尽管预备语句主要是为了避免重复对语句进行解析分析以及规划，但是只要上一次使用该预备语句后该语句中用到的数据库对象发生了定义性（DDL）改变，将会对该语句强制进行重新分析和重新规划。还有，如果search_path的值发生变化，也将使用新的search_path重新解析该语句。这些规则让预备语句的使用在语义上几乎等效于反复提交相同的查询文本，但是能在性能上获利（如果没有对象定义被改变，特别是如果最优计划保持不变时）。该语义等价性不完美的一个例子是：如果语句用一个未限定的名称引用表，并且之后在search_path中更靠前的模式中创建了一个新的同名表，则不会发生自动的重解析，因为该语句使用的对象没有被改变。不过，如果某些其他更改造成了重解析，后续使用中都会引用新表。

可以通过查询pg_prepared_statements系统视图来看到会话中所有可用的预备语句。

• 例子

--创建测试用表
create table test(a int, b text, c bool, d numeric);

--为一个INSERT语句创建一个预备语句，然后执行它：
PREPARE testplan(int, text, bool, numeric) AS INSERT INTO test VALUES($1, $2, $3, $4);
EXECUTE testplan(1, 'Hunter Valley', 't', 200.00);

--为一个SELECT语句创建一个预备语句，然后执行它：
PREPARE selectplan(int) AS SELECT * FROM test WHERE a=$1;
EXECUTE selectplan(1); 

EXECUTE 执行一个预备语句

EXECUTE name [ ( parameter [, ...] ) ]

• name 要执行的预备语句的名称。
• parameter 给预备语句的参数的实际值。这必须是一个能得到与该参数数据类型（ 在预备语句创建时决定）兼容的值的表达式。

EXECUTE被用来执行一个之前准备好的语句。 由于预备语句只在会话期间存在，该预备语句必须在当前会话中由一个更早 执行的PREPARE语句所创建。

如果创建预备语句的PREPARE语句指定了一些参数， 必须向EXECUTE语句传递一组兼容的参数，否则会 发生错误。注意（与函数不同）预备语句无法基于其参数的类型或者数量重载。 在一个数据库会话中，预备语句的名称必须唯一。

EXECUTE返回的命令标签是预备语句的命令标签而不是 EXECUTE。

DEALLOCATE 释放一个预备语句

DEALLOCATE [ PREPARE ] { name | ALL }

• PREPARE 这个关键词会被忽略。
• name 要释放的预备语句的名称。
• ALL 释放所有预备语句。

DEALLOCATE被用来释放一个之前 准备好的 SQL 语句。如果不显式地释放一个预备语句，会话结束 时会释放它。

EXPLAIN 显示一个语句的执行计划

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

这里 option可以是：

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

• ANALYZE 执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为FALSE。
• VERBOSE 显示关于计划的额外信息。特别是：计划树中每个结点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。这个参数默认被设置为FALSE。
• COSTS 包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。
• BUFFERS 包括缓冲区使用的信息。特别是：共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、以及临时块读取和写入的次数。一次命中表示避免了一次读取，因为需要的块已经在缓存中找到了。共享块包含着来自于常规表和索引的数据，本地块包含着来自于临时表和索引的数据，而临时块包含着在排序、哈希、物化计划结点和类似情况中使用的短期工作数据。脏块的数量表示被这个查询改变的之前未被修改块的数量，而写入块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。为一个较高层结点显示的块数包括它的所有子结点所用到的块数。在文本格式中，只会打印非零值。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为FALSE。
• TIMING 在输出中包括实际启动时间以及在每个结点中花掉的时间。反复读取系统时钟的负荷在某些系统上会显著地拖慢查询，因此在只需要实际的行计数而不是实际时间时，把这个参数设置为FALSE可能会有用。即便用这个选项关闭结点层的计时，整个语句的运行时间也总是会被度量。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为TRUE。
• FORMAT 指定输出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本输出包含和文本输出格式相同的信息，但是更容易被程序解析。这个参数默认被设置为TEXT。
• boolean 指定被选中的选项是否应该被打开或关闭。可以写TRUE、ON或1来启用选项，写FALSE、OFF或0禁用它。boolean值也能被忽略，在这种情况下会假定值为TRUE。
• statement 你想查看其执行计划的任何SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS或者CREATE MATERIALIZED VIEW AS语句。

输出

• 这个命令的结果是为statement选中的计划的文本描述，可能还标注了执行统计信息

说明

• 为了允许KingbaseES查询计划器在优化查询时能做出合理的知情决策，查询中用到的所有表的sys_statistic数据应该能保持为最新。通常这个工作会由autovacuum daemon负责自动完成。但是如果一个表最近在内容上有大量的改变，我们可能需要做一次手动的ANALYZE而不是等待 autovacuum 捕捉这些改变。

• 为了执行计划中每个结点的运行时间开销，当前的In order to measure the run-time cost of each node in the execution plan, the current implementation of EXPLAIN ANALYZE实现为查询执行增加了 profiling overhead 。这样，在一个查询上运行EXPLAIN ANALYZE有时候比正常执行该查询要慢很多。开销的量取决于该查询的性质，以及使用的平台。最坏的情况会发生在那些自身执行时间很短的结点上，以及在那些具有相对较慢的有关时间的操作系统调用的机器上。

例子

--创建查询测试表
create table test2(a int);
insert into test2 select i from generate_series(1, 10000) t(i);

--有一个具有单个integer列和 10000 行的表，要显示在其上的一个简单查询的计划：
POSTGRES=# EXPLAIN SELECT * FROM test2;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on TEST2  (cost=0.00..145.00 rows=10000 width=4)
(1 row)

--json格式输出
POSTGRES=# EXPLAIN (FORMAT JSON) SELECT * FROM test2;
           QUERY PLAN            
---------------------------------
 [                              +
   {                            +
     "Plan": {                  +
       "Node Type": "Seq Scan", +
       "Parallel Aware": false, +
       "Relation Name": "TEST2",+
       "Alias": "TEST2",        +
       "Startup Cost": 0.00,    +
       "Total Cost": 145.00,    +
       "Plan Rows": 10000,      +
       "Plan Width": 4          +
     }                          +
   }                            +
 ]
(1 row)

--加参数ANALYZE
POSTGRES=# EXPLAIN ANALYZE  SELECT * FROM test2 WHERE a=1000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Seq Scan on TEST2  (cost=0.00..170.00 rows=1 width=4) (actual time=0.271..2.354 rows=1 loops=1)
   Filter: (A = 1000)
   Rows Removed by Filter: 9999
 Planning time: 0.045 ms
 Execution time: 2.370 ms
(5 rows)

-- 这里是一个使用EXPLAIN EXECUTE显示预备查询的执行计划的例子：
POSTGRES=# PREPARE test2plan(int) AS SELECT * FROM test2 WHERE a=$1;
PREPARE
POSTGRES=# EXPLAIN ANALYZE EXECUTE test2plan(1000);
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Seq Scan on TEST2  (cost=0.00..170.00 rows=1 width=4) (actual time=0.243..2.359 rows=1 loops=1)
   Filter: (A = 1000)
   Rows Removed by Filter: 9999
 Execution time: 2.400 ms
(4 rows)

ANALYZE 收集有关一个数据库的统计信息

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

• VERBOSE 允许显示进度消息。
• table_name 要分析的一个指定表的名称（可以是模式限定的）。如果省略，当前数据库中所有常规表（非外部表）都会被分析。
• column_name 要分析的一个指定列的名称。默认是所有列。

ANALYZE收集一个数据库中的表的内容的统计信息，并且将结果存储在sys_statistic系统目录中。接下来，查询规划器会使用这些统计信息来帮助确定查询最有效的执行计划。

如果不带参数，ANALYZE会检查当前数据库中的所有表。如果带有一个参数，ANALYZE只会检查那个表。可以进一步给出一个列名的列表，该情况下只会收集这些列的统计信息。

输出

当指定了VERBOSE时，ANALYZE会发出进度消息来指示当前正在处理哪个表。还会打印有关那些表的多种统计信息。

说明

只有被显式选中时才会分析外部表。并非所有外部数据包装器都支持ANALYZE。如果表的包装器不支持ANALYZE，该命令会打印一个警告并且什么也不做。

在默认配置中，自动清理守护进程会在表第一次载入数据或者用常规操作改变时负责表的自动分析。当启用自动清理时，定期运行ANALYZE是个好主意，或者可以在表内容做了大的修改后运行ANALYZE。准确的统计信息将帮助规划器选择最合适的查询计划，从而提升查询处理的速度。主读数据库的一般策略是在一天中使用量最低时运行一次VACUUM和ANALYZE，如果有大量的更新动作则是不够的。

ANALYZE只要求目标表上的一个读锁，因此它可以和表上的其他动作并行。

ANALYZE收集的统计信息通畅包括每列中最常见值的列表以及展示每列中近似数据分布的一个直方图。如果ANALYZE认为这些东西无趣（例如在一个唯一键列中，没有共同值）或者该列的数据类型不支持合适的操作符，以上工作都会被省略。

对于大型的表，ANALYZE会对表内容做随机采样而不是检查每一行。这允许在很少的时间内完成对大型表的分析。不过要注意，这些统计信息只是近似值，并且即使实际表内容没有改变，每次运行ANALYZE时统计信息都会有微小地改变。这可能会导致EXPLAIN显示的规划器估算代价有小的改变。在很少的情况下，这会非决定性地导致规划器的查询计划选择在ANALYZE运行后改变。为了避免这种情况，可以按照下文所述提高ANALYZE所收集的统计信息量。

通过调整default_statistics_target配置变量可以控制分析量，对每个列可以用ALTER TABLE ... ALTER COLUMN ... SET STATISTICS设置每列的统计信息目标。目标值会设置最常用值列表中的最大项数以及直方图中的最大容器数。默认目标值是 100，可以把它调大或者调小在规划器估计值精度和ANALYZE花费的时间以及sys_statistic所占空间之间做出平衡。特别地，将统计信息目标设置为零会禁用该列的统计信息收集。在查询的WHERE、GROUP BY或者ORDER BY子句中从不出现的列上这样做会有所帮助，因为规划器用不上这些列上的统计信息。

被分析的列中最大的统计信息目标决定了为准备统计信息要采样的表行数。增加该目标会导致做ANALYZE所需的时间和空间成比例增加。

ANALYZE所估算的值之一是出现在每个列中的可区分值。因为只会检查行的一个子集，即便使用最大的统计信息目标，这种估计有时也可能很不精确。如果这种不精确导致不好的查询计划，可以手工确定一个更精确的值并且用LTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)设置该值。

如果被分析的表有一个或者更多子女，ANALYZE将会收集两次统计信息：一次只对父表的行收集，第二次则在父表及其所有子女表的行上收集。在规划需要遍历整个继承树的查询时需要第二个统计信息集。不过，在决定是否触发表上的自动分析时，自动清理后台进程将只考虑父表本身上的插入和更新。如果该表很少被插入或者更新，只有手工运行ANALYZE时才会把继承统计信息更新到最新。

如果任何子表是外部表并且其外部数据包装器不支持ANALYZE，在收集继承统计信息时会忽略那些子表。

如果被分析的表不完全为空，ANALYZE将不会为该表记录新统计信息。任何现有统计信息将会被保留。