理解PG数据库执行计划与优化-CSDN博客

本文链接：https://blog.csdn.net/arcticJian/article/details/50465579

本文详细介绍了如何查看和理解PostgreSQL（PG）数据库的执行计划，通过案例展示了不同查询参数下的执行计划差异，强调了`ANALYZE`参数的影响以及查询条件对索引利用的关键作用。同时，提到了在PecStar系统中，优化查询语句以提高执行效率的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PG数据库执行计划

在TEST数据库下创建一张explainTest表，包括num和name两个字段，num为主键。并插入1000条数据。

TEST=# explain (analyze true, buffers true) select * from explainTest where num > 900;
TEST=# insert into explainTest select generate_series(1,1000),'zhangsan';
INSERT 0 1000

直接查询全表，并用默认参数值输出查询计划：

TEST=# explain select * from explainTest;
                           QUERY PLAN
----------------------------------------------------------------
Seq Scan on explaintest  (cost=0.00..16.00 rows=1000 width=13)
(1 行记录)

说明：

1. Seq Scan 表示顺序扫描全表。

2. cost=0.00..16.00，表示启动成本为0，执行完成的成本为16。成本大小和配置有关，默认成本配置如下：

        seq_page_cost = 1.0         # 顺序扫表一个数据页，cost值为1
        random_page_cost = 4.0          # 随机扫描一个数据页，cost只为4
        cpu_tuple_cost = 0.01           # 处理一个数据行的cpu，cost为0.01
        cpu_index_tuple_cost = 0.005    # 处理一个索引行的cpt，cost为0.005
        cpu_operator_cost = 0.0025      # 每个操作符的CPU，cost为0.0025
3. rows=1000，表示查询结果为1000行。
4. width=13，表示每行有13个字节。

直接查询全表，增加analyze参数输出查询计划：

TEST=# explain analyze select * from explainTest;
                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on explaintest  (cost=0.00..16.00 rows=1000 width=13) (actual time=0.012..0.082 rows=1000 loops=1)
Planning time: 0.054 ms
Execution time: 0.137 ms
(3 行记录)

说明：

增加analyze参数后，输出的计划为实际执行的计划结果。
仍然是通过全表扫表进行查询，结果中多了actual的结果，即为实际执行查询的结果。

增加查询条件执行查询

TEST=# explain analyze select * from explainTest where num = 100;
                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pk_explaintest on explaintest  (cost=0.28..8.29 rows=1 width=13) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (num = 100)
Planning time: 0.152 ms
Execution time: 0.044 ms
(4 行记录)

说明：

index scan pk_explaintest，说明通过索引扫描执行查询。索引扫描是通过索引确定所需数据的物理位置，然后再到表的数据块中读取对应的数九。
Index Cond，为查询条件。

计划中增加buffers参数

TEST=# explain (analyze true, buffers true) select * from explainTest;
                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on explaintest  (cost=0.00..16.00 rows=1000 width=13) (actual time=0.053..0.223 rows=1000 loops=1)
Buffers: shared hit=6
Planning time: 0.061 ms
Execution time: 0.340 ms
(4 行记录)

说明：

结果中的Buffers：shared hit=6，说明在共享内存中命中了6个数据块完成查询，而没有从磁盘中读取数据。

调整计划输出格式

TEST=# explain (analyze true, buffers true, format xml) select * from explainTest;
                QUERY PLAN
----------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain">+
<Query>                                               +
    <Plan>                                              +
    <Node-Type>Seq Scan</Node-Type>                   +
    <Relation-Name>explaintest</Relation-Name>        +
    <Alias>explaintest</Alias>                        +
    <Startup-Cost>0.00</Startup-Cost>                 +
    <Total-Cost>16.00</Total-Cost>                    +
    <Plan-Rows>1000</Plan-Rows>                       +
    <Plan-Width>13</Plan-Width>                       +
    <Actual-Startup-Time>0.008</Actual-Startup-Time>  +
    <Actual-Total-Time>0.081</Actual-Total-Time>      +
    <Actual-Rows>1000</Actual-Rows>                   +
    <Actual-Loops>1</Actual-Loops>                    +
    <Shared-Hit-Blocks>6</Shared-Hit-Blocks>          +
    <Shared-Read-Blocks>0</Shared-Read-Blocks>        +
    <Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>  +
    <Shared-Written-Blocks>0</Shared-Written-Blocks>  +
    <Local-Hit-Blocks>0</Local-Hit-Blocks>            +
    <Local-Read-Blocks>0</Local-Read-Blocks>          +
    <Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>    +
    <Local-Written-Blocks>0</Local-Written-Blocks>    +
    <Temp-Read-Blocks>0</Temp-Read-Blocks>            +
    <Temp-Written-Blocks>0</Temp-Written-Blocks>      +
    <I/O-Read-Time>0.000</I/O-Read-Time>              +
    <I/O-Write-Time>0.000</I/O-Write-Time>            +
    </Plan>                                             +
    <Planning-Time>0.066</Planning-Time>                +
    <Triggers>                                          +
    </Triggers>                                         +
    <Execution-Time>0.131</Execution-Time>              +
</Query>                                              +
</explain>
(1 行记录)

说明：

相比text的结果只输出的所涉及到的参数，xml格式输出的结果更多更详细。

案例

PecStar系统的存储过程中使用拼接语句判断某个表是否存在的操作，例如判断某个年份的电能表是否存储，语句为：

select * from pg_class where relname = 'pd_tb_05_'||Year;

该语句的执行计划为：

PECSTAR_DATA_TEST=# explain analyze select * from pg_class where relname = 'pd_tb_05_'||'2015';
                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on pg_class  (cost=0.00..249.69 rows=34 width=200) (actual time=0.840..1.835 rows=1 loops=1)
    Filter: ((relname)::text = 'pd_tb_05_2015'::text)
    Rows Removed by Filter: 6729
Planning time: 0.116 ms
Execution time: 1.869 ms
(5 行记录)

调整查询方式，如下：

PECSTAR_DATA_TEST=# explain analyze select * from pg_class where relname = 'pd_tb_05_2015';
                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.28..8.30 rows=1 width=200) (actual time=0.029..0.029 rows=1 loops=1)
    Index Cond: (relname = 'pd_tb_05_2015'::name)
Planning time: 0.129 ms
Execution time: 0.061 ms
(4 行记录)

可以看出，两者的耗时存在较大的差异，区别即为第一种方式查询时未走索引，第二种走了索引。原因即为where后的条件，第二种查询中的条件被认为是name类型，和relname字段类型一致。第一种被认为是text类型，和relname字段类型不一致，没有走索引。