PG执行计划

来源：互联网发布：中央营业税收入数据编辑：程序博客网时间：2024/05/24 04:12

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.00253. 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 msExecution 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 msExecution 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=6Planning time: 0.061 msExecution 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: 6729Planning time: 0.116 msExecution 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 msExecution time: 0.061 ms(4 行记录)

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

1 0