diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/auto-explain.sgml | 10 | ||||
| -rw-r--r-- | doc/src/sgml/bloom.sgml | 16 | ||||
| -rw-r--r-- | doc/src/sgml/jit.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/perform.sgml | 42 | ||||
| -rw-r--r-- | doc/src/sgml/planstats.sgml | 24 | ||||
| -rw-r--r-- | doc/src/sgml/ref/explain.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/rules.sgml | 18 |
7 files changed, 61 insertions, 61 deletions
diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml index 0c4656ee302..15c868021e6 100644 --- a/doc/src/sgml/auto-explain.sgml +++ b/doc/src/sgml/auto-explain.sgml @@ -337,13 +337,13 @@ LOG: duration: 3.651 ms plan: Query Text: SELECT count(*) FROM pg_class, pg_index WHERE oid = indrelid AND indisunique; - Aggregate (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1 loops=1) - -> Hash Join (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92 loops=1) + Aggregate (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1.00 loops=1) + -> Hash Join (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92.00 loops=1) Hash Cond: (pg_class.oid = pg_index.indrelid) - -> Seq Scan on pg_class (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255 loops=1) - -> Hash (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92 loops=1) + -> Seq Scan on pg_class (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255.00 loops=1) + -> Hash (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92.00 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 4kB - -> Seq Scan on pg_index (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92 loops=1) + -> Seq Scan on pg_index (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92.00 loops=1) Filter: indisunique ]]></screen> </sect2> diff --git a/doc/src/sgml/bloom.sgml b/doc/src/sgml/bloom.sgml index 6a8a60b8c70..663a0a4a681 100644 --- a/doc/src/sgml/bloom.sgml +++ b/doc/src/sgml/bloom.sgml @@ -118,7 +118,7 @@ SELECT 10000000 =# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN -------------------------------------------------------------------&zwsp;----------------------------------- - Seq Scan on tbloom (cost=0.00..213744.00 rows=250 width=24) (actual time=357.059..357.059 rows=0 loops=1) + Seq Scan on tbloom (cost=0.00..213744.00 rows=250 width=24) (actual time=357.059..357.059 rows=0.00 loops=1) Filter: ((i2 = 898732) AND (i5 = 123451)) Rows Removed by Filter: 10000000 Buffers: shared hit=63744 @@ -142,7 +142,7 @@ CREATE INDEX =# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN -------------------------------------------------------------------&zwsp;----------------------------------- - Seq Scan on tbloom (cost=0.00..213744.00 rows=2 width=24) (actual time=351.016..351.017 rows=0 loops=1) + Seq Scan on tbloom (cost=0.00..213744.00 rows=2 width=24) (actual time=351.016..351.017 rows=0.00 loops=1) Filter: ((i2 = 898732) AND (i5 = 123451)) Rows Removed by Filter: 10000000 Buffers: shared hit=63744 @@ -166,12 +166,12 @@ CREATE INDEX =# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------- - Bitmap Heap Scan on tbloom (cost=1792.00..1799.69 rows=2 width=24) (actual time=22.605..22.606 rows=0 loops=1) + Bitmap Heap Scan on tbloom (cost=1792.00..1799.69 rows=2 width=24) (actual time=22.605..22.606 rows=0.00 loops=1) Recheck Cond: ((i2 = 898732) AND (i5 = 123451)) Rows Removed by Index Recheck: 2300 Heap Blocks: exact=2256 Buffers: shared hit=21864 - -> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=1 width=0) (actual time=20.005..20.005 rows=2300 loops=1) + -> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=1 width=0) (actual time=20.005..20.005 rows=2300.00 loops=1) Index Cond: ((i2 = 898732) AND (i5 = 123451)) Buffers: shared hit=19608 Planning Time: 0.099 ms @@ -201,15 +201,15 @@ CREATE INDEX =# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------- - Bitmap Heap Scan on tbloom (cost=9.29..13.30 rows=1 width=24) (actual time=0.032..0.033 rows=0 loops=1) + Bitmap Heap Scan on tbloom (cost=9.29..13.30 rows=1 width=24) (actual time=0.032..0.033 rows=0.00 loops=1) Recheck Cond: ((i5 = 123451) AND (i2 = 898732)) Buffers: shared read=6 - -> BitmapAnd (cost=9.29..9.29 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1) + -> BitmapAnd (cost=9.29..9.29 rows=1 width=0) (actual time=0.047..0.047 rows=0.00 loops=1) Buffers: shared hit=6 - -> Bitmap Index Scan on btreeidx5 (cost=0.00..4.52 rows=11 width=0) (actual time=0.026..0.026 rows=7 loops=1) + -> Bitmap Index Scan on btreeidx5 (cost=0.00..4.52 rows=11 width=0) (actual time=0.026..0.026 rows=7.00 loops=1) Index Cond: (i5 = 123451) Buffers: shared hit=3 - -> Bitmap Index Scan on btreeidx2 (cost=0.00..4.52 rows=11 width=0) (actual time=0.007..0.007 rows=8 loops=1) + -> Bitmap Index Scan on btreeidx2 (cost=0.00..4.52 rows=11 width=0) (actual time=0.007..0.007 rows=8.00 loops=1) Index Cond: (i2 = 898732) Buffers: shared hit=3 Planning Time: 0.264 ms diff --git a/doc/src/sgml/jit.sgml b/doc/src/sgml/jit.sgml index 458f8acb41d..44e18bf1a6f 100644 --- a/doc/src/sgml/jit.sgml +++ b/doc/src/sgml/jit.sgml @@ -148,9 +148,9 @@ =# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------ - Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.303..0.303 rows=1 loops=1) + Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.303..0.303 rows=1.00 loops=1) Buffers: shared hit=14 - -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.017..0.111 rows=356 loops=1) + -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.017..0.111 rows=356.00 loops=1) Buffers: shared hit=14 Planning Time: 0.116 ms Execution Time: 0.365 ms @@ -165,9 +165,9 @@ SET =# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------ - Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1 loops=1) + Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1.00 loops=1) Buffers: shared hit=14 - -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.019..0.052 rows=356 loops=1) + -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.019..0.052 rows=356.00 loops=1) Buffers: shared hit=14 Planning Time: 0.133 ms JIT: diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index a502a2aaba3..be4b49f62b5 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -721,13 +721,13 @@ 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) + Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 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) + -> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10.00 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) + -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10.00 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) @@ -772,23 +772,23 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------------------&zwsp;------ - Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1) + Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100.00 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 Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100.00 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) + -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000.00 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) + -> Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100.00 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) + -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100.00 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) + -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100.00 loops=1) Index Cond: (unique1 < 100) Buffers: shared hit=2 Planning: @@ -814,7 +814,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------- - Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1) + Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000.00 loops=1) Filter: (ten < 7) Rows Removed by Filter: 3000 Buffers: shared hit=345 @@ -838,7 +838,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; QUERY PLAN -------------------------------------------------------------------&zwsp;----------------------------------- - Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1) + Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0.00 loops=1) Filter: (f1 @> '((0.5,2))'::polygon) Rows Removed by Filter: 7 Buffers: shared hit=1 @@ -858,7 +858,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------------------- - 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 Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0.00 loops=1) Index Cond: (f1 @> '((0.5,2))'::polygon) Rows Removed by Index Recheck: 1 Buffers: shared hit=1 @@ -888,13 +888,13 @@ EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND un 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) + Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10.00 loops=1) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) Heap Blocks: exact=10 - -> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1) - -> 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) + -> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0.00 loops=1) + -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100.00 loops=1) Index Cond: (unique1 < 100) - -> 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) + -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999.00 loops=1) Index Cond: (unique2 > 9000) Planning Time: 0.162 ms Execution Time: 0.143 ms @@ -916,12 +916,12 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------------------------- - Update on tenk1 (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0 loops=1) - -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1) + Update on tenk1 (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1) + -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 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) + -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1) Index Cond: (unique1 < 100) Buffers: shared read=2 Planning Time: 0.151 ms @@ -1055,9 +1055,9 @@ 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) + Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2.00 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 Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2.00 loops=1) Index Cond: (unique2 > 9000) Filter: (unique1 < 100) Rows Removed by Filter: 287 diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml index a909a5e3134..068b804a18d 100644 --- a/doc/src/sgml/planstats.sgml +++ b/doc/src/sgml/planstats.sgml @@ -492,7 +492,7 @@ SELECT relpages, reltuples FROM pg_class WHERE relname = 't'; EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;------------ - Seq Scan on t (cost=0.00..170.00 rows=100 width=8) (actual rows=100 loops=1) + Seq Scan on t (cost=0.00..170.00 rows=100 width=8) (actual rows=100.00 loops=1) Filter: (a = 1) Rows Removed by Filter: 9900 </programlisting> @@ -509,7 +509,7 @@ EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1; EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;---------- - Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1) + Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=100.00 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 </programlisting> @@ -533,7 +533,7 @@ ANALYZE t; EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;------------ - Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1) + Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100.00 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 </programlisting> @@ -554,9 +554,9 @@ EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1 EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a; QUERY PLAN -------------------------------------------------------------------&zwsp;---------------------- - HashAggregate (cost=195.00..196.00 rows=100 width=12) (actual rows=100 loops=1) + HashAggregate (cost=195.00..196.00 rows=100 width=12) (actual rows=100.00 loops=1) Group Key: a - -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=4) (actual rows=10000 loops=1) + -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=4) (actual rows=10000.00 loops=1) </programlisting> But without multivariate statistics, the estimate for the number of groups in a query with two columns in <command>GROUP BY</command>, as @@ -565,9 +565,9 @@ EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a; EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------- - HashAggregate (cost=220.00..230.00 rows=1000 width=16) (actual rows=100 loops=1) + HashAggregate (cost=220.00..230.00 rows=1000 width=16) (actual rows=100.00 loops=1) Group Key: a, b - -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000 loops=1) + -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000.00 loops=1) </programlisting> By redefining the statistics object to include n-distinct counts for the two columns, the estimate is much improved: @@ -578,9 +578,9 @@ ANALYZE t; EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------- - HashAggregate (cost=220.00..221.00 rows=100 width=16) (actual rows=100 loops=1) + HashAggregate (cost=220.00..221.00 rows=100 width=16) (actual rows=100.00 loops=1) Group Key: a, b - -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000 loops=1) + -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000.00 loops=1) </programlisting> </para> @@ -618,7 +618,7 @@ ANALYZE t; EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;------------ - Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1) + Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100.00 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 </programlisting> @@ -675,7 +675,7 @@ SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 10; QUERY PLAN -------------------------------------------------------------------&zwsp;-------- - Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1) + Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0.00 loops=1) Filter: ((a = 1) AND (b = 10)) Rows Removed by Filter: 10000 </programlisting> @@ -688,7 +688,7 @@ EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1 EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a <= 49 AND b > 49; QUERY PLAN -------------------------------------------------------------------&zwsp;-------- - Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1) + Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0.00 loops=1) Filter: ((a <= 49) AND (b > 49)) Rows Removed by Filter: 10000 </programlisting> diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 652ece7213a..7daddf03ef0 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -500,11 +500,11 @@ EXPLAIN ANALYZE EXECUTE query(100, 200); QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------------------ - HashAggregate (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1) + HashAggregate (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1) Group Key: foo Batches: 1 Memory Usage: 24kB Buffers: shared hit=4 - -> Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99 loops=1) + -> Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1) Index Cond: ((id > 100) AND (id < 200)) Buffers: shared hit=4 Planning Time: 0.244 ms diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 9fdf8b1d917..1d9924a2a3c 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1029,8 +1029,8 @@ SELECT count(*) FROM words WHERE word = 'caterpiler'; With <command>EXPLAIN ANALYZE</command>, we see: <programlisting> - Aggregate (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1) - -> Foreign Scan on words (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1) + Aggregate (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1.00 loops=1) + -> Foreign Scan on words (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0.00 loops=1) Filter: (word = 'caterpiler'::text) Rows Removed by Filter: 479829 Foreign File: /usr/share/dict/words @@ -1042,8 +1042,8 @@ SELECT count(*) FROM words WHERE word = 'caterpiler'; If the materialized view is used instead, the query is much faster: <programlisting> - Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1) - -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1) + Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1.00 loops=1) + -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0.00 loops=1) Index Cond: (word = 'caterpiler'::text) Heap Fetches: 0 Planning time: 0.164 ms @@ -1073,11 +1073,11 @@ SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10; </programlisting> <programlisting> - Limit (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1) - -> Sort (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1) + Limit (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10.00 loops=1) + -> Sort (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10.00 loops=1) Sort Key: ((word <-> 'caterpiler'::text)) Sort Method: top-N heapsort Memory: 25kB - -> Foreign Scan on words (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1) + -> Foreign Scan on words (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829.00 loops=1) Foreign File: /usr/share/dict/words Foreign File Size: 4953699 Planning time: 0.128 ms @@ -1087,8 +1087,8 @@ SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10; Using the materialized view: <programlisting> - Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1) - -> Index Scan using wrd_trgm on wrd (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1) + Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10.00 loops=1) + -> Index Scan using wrd_trgm on wrd (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10.00 loops=1) Order By: (word <-> 'caterpiler'::text) Planning time: 0.196 ms Execution time: 198.640 ms |