Have EXPLAIN ANALYZE output planning time (Andreas Karlsson)
Have EXPLAIN print the grouping columns in Agg and Group nodes (Tom Lane)
Have EXPLAIN ANALYZE show bitmap heap scan exact/lossy block information (Etsuro Fujita)
简单测试如下：
Explain Analyze 输出包含 Planning Time
9.3 版本
1 2 3 4 5 6
francs=> explain analyze select 1; QUERY PLAN ------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.006 rows=1 loops=1) Total runtime: 0.185 ms (2 rows)
9.4 版本
1 2 3 4 5 6 7
francs=> explain analyze select 1; QUERY PLAN ------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1) Planning time: 0.116 ms Execution time: 0.128 ms (3 rows)
备注：可以看到 9.4 版本的 explain analyze 命令输出包含了 Planning time 信息。
Explain 输出包含 Group 字段名称
9.3 版本
1 2 3 4 5 6
francs=> explain select id,count(*) from test_1 group by id; QUERY PLAN -------------------------------------------------------------------------------------------- GroupAggregate (cost=0.29..3934.29 rows=100000 width=4) -> Index Only Scan using idx_test_1 on test_1(cost=0.29..2434.29 rows=100000 width=4) (2 rows)
9.4 版本
1 2 3 4 5 6 7 8
francs=> explain select id,count(*) from test_1 group by id; QUERY PLAN ---------------------------------------------------------------------------------------------- GroupAggregate (cost=0.42..44678.43 rows=1000000 width=4) Group Key: id -> Index Only Scan using idx_test_1 on test_1(cost=0.42..29678.42 rows=1000000 width=4) Planning time: 0.301 ms (4 rows)
备注：9.4 版本 explain 命令输出包含了 Group Key 信息。
Bitmap Heap Scan 中包含 Block 信息
9.3 版本
1 2 3 4 5 6 7 8 9 10 11
francs=> explain analyze select id,name from test_1 where id<100 or id>999900; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test_1(cost=5.39..149.72 rows=100 width=10) (actual time=0.157..0.226 rows=99 loops=1) Recheck Cond: ((id < 100) OR (id > 999900)) -> BitmapOr (cost=5.39..5.39 rows=100 width=0) (actual time=0.038..0.038 rows=0 loops=1) -> Bitmap Index Scan on idx_test_1(cost=0.00..3.04 rows=100 width=0) (actual time=0.025..0.025 rows=99 loops=1) Index Cond: (id < 100) -> Bitmap Index Scan on idx_test_1(cost=0.00..2.30 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (id > 999900) Total runtime: 0.336 ms
9.4 版本
1 2 3 4 5 6 7 8 9 10 11 12 13 14
francs=> explain analyze select id,name from test_1 where id<100 or id>999900; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test_1(cost=10.47..702.88 rows=203 width=11) (actual time=7.860..7.933 rows=199 loops=1) Recheck Cond: ((id < 100) OR (id > 999900)) Heap Blocks: exact=3 -> BitmapOr (cost=10.47..10.47 rows=203 width=0) (actual time=7.841..7.841 rows=0 loops=1) -> Bitmap Index Scan on idx_test_1(cost=0.00..5.19 rows=102 width=0) (actual time=0.016..0.016 rows=99 loops=1) Index Cond: (id < 100) -> Bitmap Index Scan on idx_test_1(cost=0.00..5.17 rows=100 width=0) (actual time=7.822..7.822 rows=100 loops=1) Index Cond: (id > 999900) Planning time: 59.523 ms Execution time: 8.024 ms (10 rows)
