语法

explain (analyze true|false,verbose true|false,costs true|false,buffers true|false,format text|xml|json|yaml)

analyze:真实执行sql获取执行计划,dml语句不想改变数据库数据可放入事务,执行完后回滚,该选项默认值为false。
verbose:用于显示计划的附加信息,附加信息有计划熟中每个节点输出的各个列,如果触发器被触发,还会输出触发器的名称,该选项默认值为false。
costs:显示每个计划节点的启动成本和总成本,以及估计行数和每行宽度,该选项默认值为true。
buffers:显示关于缓冲器使用的信息,只能与analyze参数一起使用,显示的缓冲区信息包括共享块、本地块、临时块的读写块数,表、索引、临时表、临时索引及排序和物化计划中使用的磁盘块,上层节点使用的块数包含所有节点使用的块数。该选项默认值为false。
format:指定数据格式,可以是text、xml、json、yaml,默认值为text。

临时改变执行计划

类似 oracle 的 hint 干预执行计划,pg也有 pg_hint 插件,后期再研究。
通常情况下,pg 不会走错执行计划,走错大都是因为统计信息收集不及时导致的,可通过更频繁地运行 analyze 来解决这个问题,更改下列参数只是一个临时方法。(如下参数值均为布尔类型)

set enable_seqscan = off;

enable_seqscan:是否选择全表顺序扫描,把这个变量关闭会让优化器存在其他方法时,优先选择其他方法。
enable_indexscan:是否选择索引扫描。
enable_bitmapscan:是否选择位图扫描。
enable_tidscan:是否选择 TID 扫描。
enable_nestloop:多表连接时,是否选择嵌套循环连接。
enable_hashjoin:多表连接时,是否选择 hash 连接。
enable_mergejoin:多表连接时,是否选择 merge 连接。
enable_hashagg:是否使用 hash 聚合。
enable_sort:是否选择排序。

执行计划路径方式

全表扫描(顺序扫描):seq scan,所有数据块,从头扫到尾。
索引扫描:index scan,在索引中找到数据行的位置,然后到表的数据块中把对应的数据读出。
位图索引扫描:bitmap index scan,把满足条件的行或块在内存中建一个位图,扫描完索引后,再根据位图把表的数据文件中相应的数据读取出来。
条件过滤:filter
嵌套循环连接:nestloop join,外表(驱动表)小,内表(被驱动表)大
散列连接:hash join,用较小的表在内存中建立散列表,再去扫描较大的表,连接的表均为小表。
合并连接:merge join,通常散列连接比合并连接性能好,当有索引或结果已经被排序时,合并连接性能好。

示例

--估算
chis=> explain select * from patient;
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on patient  (cost=0.00..106205.66 rows=2406066 width=655)
(1 row)

--实际运行
chis=> explain analyze select * from patient;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on patient  (cost=0.00..106205.66 rows=2406066 width=655) (actual time=0.016..13198.645 rows=2406066 loops=1)
 Planning Time: 0.158 ms
 Execution Time: 13293.205 ms
(3 rows)

--查询页面读取数和扫描行数
chis=> SELECT relpages, reltuples FROM pg_class WHERE relname = 'patient';
 relpages |  reltuples
----------+--------------
    82145 | 2.406066e+06

--成本代价
cost = relpages(页面读取数)*seq_page_cost(1.0) + reltuples(扫描的行数)*cpu_tuple_cost(0.01) + reltuples(扫描的行数)*cpu_operator_cost(0.0025)

上述sql没有过滤条件,所以最后的cpu处理成本不用加成本为:
82145x1 + 2406066x0.01 = 106205.66

统计信息配置项

track_counts:是否收集表和索引上访问的统计信息,默认打开。
track_functions:是否收集函数调用次数和时间的统计信息。可取 none,pl,all 三个值。none 表示不收集,pl 表示只收集过程语言函数,all 表示收集所有函数,包括 SQL 和 C 语言函数。默认为 none。
track_activities:是否允许跟踪每个 session 正执行的 sql 命令的信息和命令开始时间。这些信息可在视图 pg_stat_activity中看到。此参数默认为打开的。
track_activity_query_size:在 pg_stat_activity 视图中的 query 字段最多显示多少字节,默认是 1024,超过此设置的内容被截断。
track_io_timing:是否允许统计 IO 调用的时间,默认关闭。在带有 buffers 选项的 explain 命令中将显示 io 调用时间,这些 io 统计信息也可以在 pg_stat_database 和 pg_stat_statements 中看到,这是 pg9.2 后才新加的参数。
update_process_title:当后台服务进程正在执行命令(如一条 SQL)时,是否更新其 title 信息。在 linux 下这个参数默认是打开的,所以在 linux 下,默认可以使用 ps 命令查看一个后台服务进程是否正在执行命令。
stats_temp_directory:设置存储临时统计数据的路径,可以是一个相对于数据目录的相对路径,也可以是一个绝对路径。默认值是 pg_stat_tmp。

手工收集统计信息

在 postgresql 默认配置中,autovacuum 守护进程是打开的,它能自动地分析表,并收集表的统计信息。

analyze [verbose] [table [(column [,...])]]

verbose:显示处理进度,以及表的一些统计信息。
table:分析的表名,不指定则分析整个数据库中的表。
column:分析表的特定字段,不指定默认所有字段。

设置字段有多少个唯一值

理解成 oracle 的直方图

alter table test_tab alter column id2 set (n_distinct=2000);
alter table test_tab alter column id2 set (n_distinct_inherited=2000);    --子表可继承
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐