digoal
2020-11-05
PostgreSQL , 调试
explain, parser, execute 过程资源使用性能分析.
《PostgreSQL merge join 扫描方法实例细说，以及SQL写法注意 或 内核优化建议 - query rewrite》
例子如上, 莫名其妙的plan耗时.
其他例子: 大量表的JOIN以及join_collapse_limit太大, 导致全join tables组合排列组合下的plan计算浪费.
# - Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
postgres=# set log_planner_stats=on;
SET
postgres=# set client_min_messages =log;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from pg_class where oid=1;
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
LOG: PLANNER STATISTICS
DETAIL: ! system usage stats:
! 0.000046 s user, 0.000031 s system, 0.000075 s elapsed
! [0.007455 s user, 0.004970 s system total]
! 7940 kB max resident size
! 0/0 [160/0] filesystem blocks in/out
! 0/0 [0/1696] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [21/0] voluntary/involuntary context switches
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_catalog.pg_class (cost=0.27..2.49 rows=1 width=265) (actual time=0.013..0.014 rows=0 loops=1)
Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relha
srules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound
Index Cond: (pg_class.oid = '1'::oid)
Buffers: shared hit=2
Planning Time: 0.110 ms
Execution Time: 0.033 ms
(6 rows)
perf, 可以诊断到代码层的消耗. 例子如下:
《PostgreSQL 源码性能诊断(perf profiling)指南 - 珍藏级》
《PostgreSQL log AND trace AND debug 详解》
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
