执行计划

2019独角兽企业重金招聘Python工程师标准>>>

• SQL执行过程
• 获取执行计划
  • explain
• 阅读执行计划
  • 访问对象的方式
  • 对象的关联方式
  • 对结果集的处理
  • 实际执行计划示例
• 执行计划的影响因子
  • 系统参数
  • 统计信息
    • 表的统计信息
    • 表上列的统计信息
    • 索引的统计信息
    • 统计信息的产生方式
    • 统计信息的存放位置
  • 垃圾数据
    • 垃圾数据的产生
    • 垃圾数据的影响
    • 垃圾数据的清理
• 学习链接

SQL执行过程

官方文档 48.1:

1. The application program transmits a query to the server and waits to receive the results sent back by the server。
2. The parser stage checks the query transmitted by the application program for correct syntax and creates a query tree.
3. The rewrite system takes the query tree created by the parser stage and looks for any rules (stored in the system catalogs) to apply to the query tree. It performs the transformations given in the rule bodies.
4. The planner/optimizer takes the (rewritten) query tree and creates a query plan that will be the input to the executor.
5. The executor recursively steps through the plan tree and retrieves rows in the way represented by the plan. The executor makes use of the storage system while scanning relations, performs sorts and joins, evaluates qualifications and finally hands back the rows derived.

网上文章：

1. Transmission of query string to database backend
   推送查询语句到数据库后台
2. Parsing of query string
   解析查询语句
3. Planning of query to optimize retrieval of data
   指定查询计划
4. Retrieval of data from hardware
   从数据文件检索数据
5. Transmission of results to client
   查询结果推送到客户端

自己的理解：

1. 服务器进程接收来自客户端的查询请求。
2. 解析模块对语句进行词法、语法、语义等检查并生成查询树。
3. 重写模块接收解析模块生成的查询树，并根据系统内置的规则对语句进行重写。
4. 优化模块接收重写模块重新生成的查询树，并以代价最小为基本原则生成查询计划。
5. 执行模块接收优化模块生成的查询计划，从存储系统(内存或者磁盘）中提取每一步需要的数据，并执行一些关联或者排序操作等，将最终的结果集返回给客户端程序。

获取执行计划

explain

官方帮助：

user01@testdb:5432 > \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 sql; 是根据当前的统计信息来估算出执行计划，并不真正的执行SQL。
• ANALYZE 默认为false。explain analyze 会真正的执行SQL去获取真实的执行计划，如果想使用该选项来获取insert、delete、update的执行计划，建议通过begin来手工控制事务，获取plan之后rollback即可。
• VERBOSE 默认为false。显示额外的信息，比如每个plan node上字段列表，函数名等。
• COSTS 默认为true。每个plan node的启动成本和总成本以及行数和行宽度的预估。
• BUFFERS 默认为false。显示共享块命中、读、脏和写的次数,本地块命中、读、脏和写的次数，临时块读和写的次数。该参数只有在同时使用analyze的时候才会输出信息。
• TIMING 默认为true。每个plan node的启动时间和总时间。只有在使用analyze的时候才生效。
• FORMAT 默认为text。定义plan输出的格式。

多个参数一起使用示例：

explain (analyze,buffers,verbose) select * from test where id <10;  

阅读执行计划

访问对象的方式

对表的访问：

• seq scan : 也就是全表扫描

对索引的访问：

• index scan
  
• bitmap heap scan
• bitmap index scan
• index only scan

对象的关联方式

• nest loop join
• hash join
• merge join

对结果集的处理

• Materialize: 对下层节点返回的元组进行缓存
• Sort: 对下层节点返回的元组进行排序
• Group: 对下层节点返回的元组进行分组
• Agg: 执行聚集函数
• Hash: hash join的辅助操作
• SetOp: 处理集合操作

实际执行计划示例

 (cost=4279.39..16663.99 rows=1164 width=299)

估算的启动成本，总成本，返回行数和行宽度。

(actual time=423.658..4640.919 rows=51315 loops=1)

启用analyze后，实际执行的启动时间，总时间，返回行数和循环次数。

Filter: (state = 1::numeric)
Rows Removed by Filter: 180239

在执行seq scan时候的过滤条件，通过该条件过滤掉得行数。

Index Cond: (org_channel_type = 80001::numeric)
Index Cond: (channel_id = sec.organize_id)

使用索引过滤数据和使用索引列来做表关联。

Hash  (cost=4040.43..4040.43 rows=5079 width=58) (actual time=416.210..416.210 rows=231018 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 13536kB

hash join 操作中Hash操作所消耗的资源。

Output: test.id, test.name, test.modify_date

explain使用verbose参数的时候，输出结果集的字段名称。

Buffers: shared hit=9

explain使用buffers参数的时候，输出相应的结果：从buffer中读取9个block。

执行计划的影响因子

系统参数

• cpu_index_tuple_cost
• cpu_operator_cost
• cpu_tuple_cost
• random_page_cost
• seq_page_cost
• cursor_tuple_fraction
• default_statistics_target
• enable_bitmapscan
• enable_fast_query_shipping
• enable_hashagg
• enable_hashjoin
• enable_indexonlyscan
• enable_indexscan
• enable_material
• enable_mergejoin
• enable_nestloop
• enable_remotegroup
• enable_remotejoin
• enable_remotelimit
• enable_remotesort
• enable_seqscan
• enable_sort
• enable_tidscan
• from_collapse_limit
• join_collapse_limit
• geqo
• geqo_effort
• geqo_generations
• geqo_pool_size
• geqo_seed
• geqo_selection_bias
• geqo_threshold

可以通过下面语句来获取这些参数：

select distinct category from pg_settings order by 1;
select name,setting,unit,context,short_desc,category
from pg_settings 
where category like 'Query Tuning%'
order by 6,1;

统计信息

表的统计信息

表上的统计信息主要包括：

• 表的记录数
• 表的大小

表上列的统计信息

表上列的统计信息主要包括：

• 字段的distinct值
• 字段的most_common_value
• 字段的直方图
• 字段的选择性

索引的统计信息

• 索引的记录数
• 索引的大小

统计信息的产生方式

• analyze
  analyze的官方帮助：

  Command:     ANALYZE
  Description: collect statistics about a database
  Syntax:
  ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]
  

统计信息的存放位置

• pg_class
• pg_statistics
• pg_stat

垃圾数据

垃圾数据的产生

由于MVCC的使用，对于表中数据的update和delete，以前的老数据还是存放在表中，形成dead tuple，从而导致表文件越来越大。

垃圾数据的影响

在扫描表和索引的时候，会扫描这些dead tuple，影响优化器生成最优的执行计划，从而影响数据库的性能。

垃圾数据的清理

• vacuum
  vacuum的官方帮助：

  Command:     VACUUM
  Description: garbage-collect and optionally analyze a database
  Syntax:
  VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table_name [ (column_name [, ...] ) ] ]
  VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ]
  VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, ...] ) ] ]
  

VACUUM ANALYZE先执行一个VACUUM 然后是给每个选定的表执行一个ANALYZE。
简单的VACUUM(没有FULL)只是简单地回收空间并且令其可以再次使用。 这种形式的命令可以 和对表的普通读写并发操作,因为没有请求排他锁。然而, 额外的空间并不返回到操作系统 (在大多数情况下);仅保持在相同的表中可重用。 VACUUM FULL将表的全部内容重写到一 个没有任何多余空间的新磁盘文件中, 允许未使用的空间返回到操作系统中。这种形式要慢 许多并且在处理的时候需要在表上施加一个排它锁。

• autovacuum

  PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples. These checks use the statistics collection facility; therefore, autovacuum cannot be used unless track_counts is set to true. In the default configuration, autovacuuming is enabled and the related configuration parameters are appropriately set.

autovacumm 配置：

 # -- Query/Index Statistics Collector --
track_counts = on
track_activity_query_size = 2048

# -- Autovacuum --
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 1d
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = 0.5
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_cost_delay = 30ms

当update,delete的tuples数量超过autovacuum_vacuum_scale_factortable_tuples+autovacuum_vacuum_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。
当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factortable_tuples+autovacuum_analyze_threshold时，进行analyze。

学习链接

• 可视化plan
• Analyzing and optimizing SQL queries in PostgreSQL
• PostgreSQL Query Tuning - Indices
• postgresql 执行计划理解
• Performance Tuning PostgreSQL
• PostgreSQL的查询处理概述
• [Postgres] 如何读懂执行计划：参数解释
• [Postgres] 如何读懂执行计划：计划节点
• PostgreSQL执行计划Explain
• PostgreSQL vacuum原理一功能与参数
• Managing VACUUM on Heroku Postgres
• Don’t stop PostgreSQL’s autovacuum with your application
• Detecting table bloat
• PostgreSQL Bloat: origins, monitoring and managing

转载于:https://my.oschina.net/yafeishi/blog/742316