diff options
Diffstat (limited to 'doc/src/sgml/perform.sgml')
| -rw-r--r-- | doc/src/sgml/perform.sgml | 44 |
1 files changed, 31 insertions, 13 deletions
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index cd12b9ce48b..a502a2aaba3 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -39,7 +39,7 @@ <para> Examples in this section are drawn from the regression test database - after doing a <command>VACUUM ANALYZE</command>, using v17 development sources. + after doing a <command>VACUUM ANALYZE</command>, using v18 development sources. You should be able to get similar results if you try the examples yourself, but your estimated costs and row counts might vary slightly because <command>ANALYZE</command>'s statistics are random samples rather @@ -722,13 +722,19 @@ WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------------- Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1) + Buffers: shared hit=36 read=6 -> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1) Recheck Cond: (unique1 < 10) Heap Blocks: exact=10 + Buffers: shared hit=3 read=5 written=4 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1) Index Cond: (unique1 < 10) + Buffers: shared hit=2 -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10) Index Cond: (unique2 = t1.unique2) + Buffers: shared hit=24 read=6 + Planning: + Buffers: shared hit=15 dirtied=9 Planning Time: 0.485 ms Execution Time: 0.073 ms </screen> @@ -769,16 +775,24 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1) Sort Key: t1.fivethous Sort Method: quicksort Memory: 74kB + Buffers: shared hit=440 -> Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1) Hash Cond: (t2.unique2 = t1.unique2) + Buffers: shared hit=437 -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1) + Buffers: shared hit=345 -> Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 35kB + Buffers: shared hit=92 -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1) Recheck Cond: (unique1 < 100) Heap Blocks: exact=90 + Buffers: shared hit=92 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1) Index Cond: (unique1 < 100) + Buffers: shared hit=2 + Planning: + Buffers: shared hit=12 Planning Time: 0.187 ms Execution Time: 3.036 ms </screen> @@ -803,6 +817,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1) Filter: (ten < 7) Rows Removed by Filter: 3000 + Buffers: shared hit=345 Planning Time: 0.102 ms Execution Time: 2.145 ms </screen> @@ -826,6 +841,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1) Filter: (f1 @> '((0.5,2))'::polygon) Rows Removed by Filter: 7 + Buffers: shared hit=1 Planning Time: 0.039 ms Execution Time: 0.033 ms </screen> @@ -845,6 +861,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1) Index Cond: (f1 @> '((0.5,2))'::polygon) Rows Removed by Index Recheck: 1 + Buffers: shared hit=1 Planning Time: 0.039 ms Execution Time: 0.098 ms </screen> @@ -857,34 +874,31 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; </para> <para> - <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with - <literal>ANALYZE</literal> to get even more run time statistics: + <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option which + provides additional detail about I/O operations performed during the + planning and execution of the given query. The buffer numbers displayed + show the count of the non-distinct buffers hit, read, dirtied, and written + for the given node and all of its child nodes. The + <literal>ANALYZE</literal> option implicitly enables the + <literal>BUFFERS</literal> option. If this + is undesired, <literal>BUFFERS</literal> may be explicitly disabled: <screen> -EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; +EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) Heap Blocks: exact=10 - Buffers: shared hit=14 read=3 -> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1) - Buffers: shared hit=4 read=3 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1) Index Cond: (unique1 < 100) - Buffers: shared hit=2 -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1) Index Cond: (unique2 > 9000) - Buffers: shared hit=2 read=3 - Planning: - Buffers: shared hit=3 Planning Time: 0.162 ms Execution Time: 0.143 ms </screen> - - The numbers provided by <literal>BUFFERS</literal> help to identify which parts - of the query are the most I/O-intensive. </para> <para> @@ -906,8 +920,10 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1) Recheck Cond: (unique1 < 100) Heap Blocks: exact=90 + Buffers: shared hit=4 read=2 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1) Index Cond: (unique1 < 100) + Buffers: shared read=2 Planning Time: 0.151 ms Execution Time: 1.856 ms @@ -1040,10 +1056,12 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------------------------ Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1) + Buffers: shared hit=16 -> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1) Index Cond: (unique2 > 9000) Filter: (unique1 < 100) Rows Removed by Filter: 287 + Buffers: shared hit=16 Planning Time: 0.077 ms Execution Time: 0.086 ms </screen> |