postgresql psql explain选项使用示例介绍
explain
postgres=# explain select count(*) from pgbench_accounts ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=2854.29..2854.30 rows=1 width=8)
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..2604.29 rows=100000 width=0)
(2 rows)
postgres=#
explain analyze
postgres=# explain analyze select count(*) from pgbench_accounts ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2854.29..2854.30 rows=1 width=8) (actual time=26.809..26.809 rows=1 loops=1)
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..2604.29 rows=100000 width=0) (actual time=0.033..14.879 rows=100000 loops=1)
Heap Fetches: 0
Planning time: 0.099 ms
Execution time: 26.877 ms
(5 rows)
explain (analyze,buffers)
联合使用analyze和buffers选项
注意:buffers必须跟analyze一起使用,只用真实执行SQL才能获取缓冲区信息),可通过实际执行SQL查看实际的代价和缓冲区命中的情况,
postgres=# explain (analyze,buffers) select count(*) from pgbench_accounts ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2854.29..2854.30 rows=1 width=8) (actual time=28.387..28.387 rows=1 loops=1)
Buffers: shared hit=276
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..2604.29 rows=100000 width=0) (actual time=0.019..14.825 rows=100000 loops=1)
Heap Fetches: 0
Buffers: shared hit=276
Planning time: 0.129 ms
Execution time: 28.438 ms
(7 rows)
postgres=#
explain (analyze,verbose,buffers,costs,timing)
postgres=# explain (analyze,verbose,buffers,costs,timing) select count(*) from pgbench_accounts ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2854.29..2854.30 rows=1 width=8) (actual time=28.262..28.262 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=276
-> Index Only Scan using pgbench_accounts_pkey on public.pgbench_accounts (cost=0.29..2604.29 rows=100000 width=0) (actual time=0.019..14.475 rows=100000 loops=1)
Output: aid
Heap Fetches: 0
Buffers: shared hit=276
Planning time: 0.171 ms
Execution time: 28.297 ms
(9 rows)
参考
1. explain (analyze,verbose,buffers,costs,timing) select
2. Postgresql explain的analyze和buffers选项