2.1 理解代价模型

创建测试数据：

CREATE TABLE t_test (
    id serial,
    name text
);


INSERT INTO t_test (
    name
)
SELECT
    'hans'
FROM
    generate_series(1, 2000000);


INSERT INTO t_test (
    name
)
SELECT
    'paul'
FROM
    generate_series(1, 2000000);

SELECT
    name, 
    count(*) 
FROM
   t_test
GROUP BY
    name;
    
 name |  count
------+---------
 paul | 2000000
 hans | 2000000
(2 rows)


postgres=# \timing
Timing is on.


SELECT
    * 
FROM
    t_test
WHERE
    id = 432332;
    
   id   | name
--------+------
 432332 | hans
(1 row)

Time: 951.449 ms

使用EXPLAIN

读取4百万行用了超过900ms时间。为了找出什么地方不对，PostgreSQL提供了EXPLAIN命令：

postgres=# \h EXPLAIN

Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

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

下面这个列表中看到的就是执行计划：

EXPLAIN
SELECT
    *
FROM 
    t_test
WHERE
    id = 432332;
            
                               QUERY PLAN
-------------------------------------------------------------------------
 Gather  (cost=1000.00..43455.43 rows=1 width=9)
   Workers Planned: 2
   ->  Parallel Seq Scan on t_test  (cost=0.00..42455.33 rows=1 width=9)
         Filter: (id = 432332)
(4 rows)

在PostgreSQL中，一个SQL语句将被分成4个阶段执行。下列组件会参与其中：

• 解析器检查语法错误已经明显的问题；
• 重写系统负责规则（视图等）；
• 优化器解决如何以最有效的方法执行一个查询并且制定出一个计划；
• 优化器提供的计划被执行器用来创建最终结果。

EXPLAIN的目的是看看计划器给出什么样的东西来高效地运行查询。在这个例子中，PostgreSQL将使用一个并行顺序扫描，这意味着两个工作者将合作来处理过滤条件。得到的局部结果接下来通过收集节点联合起来。

并行工作者的数量由表的尺寸决定。并行并非必不可少，可以设置为0：

SET
    max_parallel_workers_per_gather
TO
    0;

深究代价模型

如果只有一个CPU被使用，执行计划看起来会是这样：

EXPLAIN
SELECT
    *
FROM 
    t_test
WHERE
    id = 432332;
    
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on t_test  (cost=0.00..71622.00 rows=1 width=9)
   Filter: (id = 43312)
(2 rows)

PostgreSQL将顺序读取整个表并且应用过滤条件。该操作将花费71622惩罚点。惩罚点是一种抽象概念，如果一个查询可以被执行器以多种方式执行，PostgreSQL将选取承诺最低代价的执行计划。

计算原理如下：

SELECT
    pg_relation_size('t_test') / 8192.0;
    
      ?column?
--------------------
 21622.000000000000
(1 row)

这个关系由21622个块构成。根据代价模型，PostgreSQL将会为它必须顺序读取的每个块加上代价1。

影响这个过程的配置参数是：

SHOW seq_page_cost;

 seq_page_cost
---------------
 1
(1 row)

不过，从磁盘读取一大堆块并非我们要做的所有事情。还需要通过CPU应用过滤条件并且发送那些行。有两个参数负责相应的代价：

SHOW cpu_tuple_cost;    -- 发送一行的代价

 cpu_tuple_cost
----------------
 0.01
(1 row)


SHOW cpu_operator_cost;    -- 对一行应用过滤条件的代价

 cpu_operator_cost
-------------------
 0.0025
(1 row)

计算如下：

SELECT
    21622*1 + 4000000*0.01 + 4000000*0.0025;

  ?column?
------------
 71622.0000
(1 row)

再举个例子：

EXPLAIN
SELECT
    *
FROM
    t_test;
    
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on t_test  (cost=0.00..61622.00 rows=4000000 width=9)
(1 row)


SELECT
    21622*1 + 4000000*0.01;
    
 ?column?
----------
 61622.00
(1 row)

PostgreSQL还有一些用于索引相关操作的特殊参数：

• random_page_cost = 4：如果PostgreSQL使用一个索引，通常会涉及很多随机IO。在机械盘上，随机读比顺序读重要得多，因此PostgreSQL也将相应地解释它们。在SSD上，随机读和顺序读之间的差异已经不再存在，因此可以设置random_page_cost = 1。
• cpu_index_tuple_cost = 0.005：如果使用了索引，PostgreSQL还将考虑一些索引的CPU代价。

如果用户使用并行查询，还有更多代价参数：

• parallel_tuple_cost = 0.1：这个参数定义了从一个并行工作者进程向另一个进程传输一个元组的代价。它基本上用于解释在并行架构内部移动元组的开销。
• parallel_setup_cost = 1000.0：这个参数调整发动一个工作者进程的代价。
• min_parallel_relation_size = 8MB：这个参数定义了考虑使用并行查询的表的最小尺寸。一个表长得越大，PostgreSQL就将考虑使用更多的CPU。表的尺寸必须成为之前3倍才会多出一个工作者进程。