diff options
| author | Robert Haas <rhaas@postgresql.org> | 2025-02-27 11:25:18 -0500 |
|---|---|---|
| committer | Robert Haas <rhaas@postgresql.org> | 2025-02-27 11:27:16 -0500 |
| commit | 95dbd827f2edc4d10bebd7e840a0bd6782cf69b7 (patch) | |
| tree | 583d6785493f5d92851a6c14a751299e954c7e60 /doc/src | |
| parent | ce62f2f2a0a48d021f250ba84dfcab5d45ddc914 (diff) | |
| download | postgresql-95dbd827f2edc4d10bebd7e840a0bd6782cf69b7.tar.gz postgresql-95dbd827f2edc4d10bebd7e840a0bd6782cf69b7.zip | |
EXPLAIN: Always use two fractional digits for row counts.
Commit ddb17e387aa28d61521227377b00f997756b8a27 attempted to avoid
confusing users by displaying digits after the decimal point only when
nloops > 1, since it's impossible to have a fraction row count after a
single iteration. However, this made the regression tests unstable since
parallal queries will have nloops>1 for all nodes below the Gather or
Gather Merge in normal cases, but if the workers don't start in time and
the leader finishes all the work, they will suddenly have nloops==1,
making it unpredictable whether the digits after the decimal point would
be displayed or not. Although 44cbba9a7f51a3888d5087fc94b23614ba2b81f2
seemed to fix the immediate failures, it may still be the case that there
are lower-probability failures elsewhere in the regression tests.
Various fixes are possible here. For example, it has previously been
proposed that we should try to display the digits after the decimal
point only if rows/nloops is an integer, but currently rows is storead
as a float so it's not theoretically an exact quantity -- precision
could be lost in extreme cases. It has also been proposed that we
should try to display the digits after the decimal point only if we're
under some sort of construct that could potentially cause looping
regardless of whether it actually does. While such ideas are not
without merit, this patch adopts the much simpler solution of always
display two decimal digits. If that approach stands up to scrutiny
from the buildfarm and human users, it spares us the trouble of doing
anything more complex; if not, we can reassess.
This commit incidentally reverts 44cbba9a7f51a3888d5087fc94b23614ba2b81f2,
which should no longer be needed.
Author: Robert Haas <robertmhaas@gmail.com>
Author: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Discussion: http://postgr.es/m/CA+TgmoazzVHn8sFOMFAEwoqBTDxKT45D7mvkyeHgqtoD2cn58Q@mail.gmail.com
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 |