8000 EXPLAIN: Always use two fractional digits for row counts. · postgrespro/postgres@95dbd82 · GitHub
[go: up one dir, main page]
Skip to content 8000

Commit 95dbd82

Browse files
robertmhaasrobertmhaas
committed
EXPLAIN: Always use two fractional digits for row counts.
Commit ddb17e3 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 44cbba9 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 44cbba9, 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
1 parent ce62f2f commit 95dbd82

25 files changed

+623
-649
lines changed

‎contrib/pg_stat_statements/expected/level_tracking.out

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -904,16 +904,16 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
904904
(1 row)
905905

906906
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 100;
907-
QUERY PLAN
908-
--------------------------------
909-
Result (actual rows=1 loops=1)
907+
QUERY PLAN
908+
-----------------------------------
909+
Result (actual rows=1.00 loops=1)
910910
(1 row)
911911

912912
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
913913
DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
914-
QUERY PLAN
915-
-----------------------------------------------------
916-
Seq Scan on stats_track_tab (actual rows=0 loops=1)
914+
QUERY PLAN
915+
--------------------------------------------------------
916+
Seq Scan on stats_track_tab (actual rows=0.00 loops=1)
917917
(1 row)
918918

919919
SELECT toplevel, calls, query FROM pg_stat_statements
@@ -937,16 +937,16 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
937937
(1 row)
938938

939939
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 100;
940-
QUERY PLAN
941-
--------------------------------
942-
Result (actual rows=1 loops=1)
940+
QUERY PLAN
941+
-----------------------------------
942+
Result (actual rows=1.00 loops=1)
943943
(1 row)
944944

945945
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
946946
DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
947-
QUERY PLAN
948-
-----------------------------------------------------
949-
Seq Scan on stats_track_tab (actual rows=0 loops=1)
947+
QUERY PLAN
948+
--------------------------------------------------------
949+
Seq Scan on stats_track_tab (actual rows=0.00 loops=1)
950950
(1 row)
951951

952952
SELECT toplevel, calls, query FROM pg_stat_statements

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 30 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -11670,15 +11670,15 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c
1167011670

1167111671
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1167211672
SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar';
11673-
QUERY PLAN
11674-
-------------------------------------------------------------------------------
11675-
Nested Loop (actual rows=1 loops=1)
11676-
-> Seq Scan on local_tbl (actual rows=1 loops=1)
11673+
QUERY PLAN
11674+
----------------------------------------------------------------------------------
11675+
Nested Loop (actual rows=1.00 loops=1)
11676+
-> Seq Scan on local_tbl (actual rows=1.00 loops=1)
1167711677
Filter: (c = 'bar'::text)
1167811678
Rows Removed by Filter: 1
11679-
-> Append (actual rows=1 loops=1)
11679+
-> Append (actual rows=1.00 loops=1)
1168011680
-> Async Foreign Scan on async_p1 async_pt_1 (never executed)
11681-
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1)
11681+
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1.00 loops=1)
1168211682
-> Seq Scan on async_p3 async_pt_3 (never executed)
1168311683
Filter: (a = local_tbl.a)
1168411684
(9 rows)
@@ -11916,20 +11916,20 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W
1191611916

1191711917
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1191811918
SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
11919-
QUERY PLAN
11920-
-----------------------------------------------------------------------------------------
11921-
Nested Loop Left Join (actual rows=1 loops=1)
11919+
QUERY PLAN
11920+
--------------------------------------------------------------------------------------------
11921+
Nested Loop Left Join (actual rows=1.00 loops=1)
1192211922
Join Filter: (t1.a = async_pt.a)
1192311923
Rows Removed by Join Filter: 399
1192411924
InitPlan 1
11925-
-> Aggregate (actual rows=1 loops=1)
11926-
-> Append (actual rows=400 loops=1)
11927-
-> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200 loops=1)
11928-
-> Async Foreign Scan on async_p2 async_pt_5 (actual rows=200 loops=1)
11929-
-> Seq Scan on local_tbl t1 (actual rows=1 loops=1)
11930-
-> Append (actual rows=400 loops=1)
11931-
-> Async Foreign Scan on async_p1 async_pt_1 (actual rows=200 loops=1)
11932-
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=200 loops=1)
11925+
-> Aggregate (actual rows=1.00 loops=1)
11926+
-> Append (actual rows=400.00 loops=1)
11927+
-> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200.00 loops=1)
11928+
-> Async Foreign Scan on async_p2 async_pt_5 (actual rows=200.00 loops=1)
11929+
-> Seq Scan on local_tbl t1 (actual rows=1.00 loops=1)
11930+
-> Append (actual rows=400.00 loops=1)
11931+
-> Async Foreign Scan on async_p1 async_pt_1 (actual rows=200.00 loops=1)
11932+
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=200.00 loops=1)
1193311933
(12 rows)
1193411934

1193511935
SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
@@ -11960,15 +11960,15 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
1196011960

1196111961
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1196211962
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
11963-
QUERY PLAN
11964-
-------------------------------------------------------------------------
11965-
Limit (actual rows=1 loops=1)
11966-
-> Append (actual rows=1 loops=1)
11967-
-> Async Foreign Scan on async_p1 t1_1 (actual rows=0 loops=1)
11963+
QUERY PLAN
11964+
----------------------------------------------------------------------------
11965+
Limit (actual rows=1.00 loops=1)
11966+
-> Append (actual rows=1.00 loops=1)
11967+
-> Async Foreign Scan on async_p1 t1_1 (actual rows=0.00 loops=1)
1196811968
Filter: (b === 505)
11969-
-> Async Foreign Scan on async_p2 t1_2 (actual rows=0 loops=1)
11969+
-> Async Foreign Scan on async_p2 t1_2 (actual rows=0.00 loops=1)
1197011970
Filter: (b === 505)
11971-
-> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1)
11971+
-> Seq Scan on async_p3 t1_3 (actual rows=1.00 loops=1)
1197211972
Filter: (b === 505)
1197311973
Rows Removed by Filter: 101
1197411974
(9 rows)
@@ -12120,12 +12120,12 @@ DELETE FROM async_p2;
1212012120
DELETE FROM async_p3;
12121< 10000 /code>12121
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1212212122
SELECT * FROM async_pt;
12123-
QUERY PLAN
12124-
-------------------------------------------------------------------------
12125-
Append (actual rows=0 loops=1)
12126-
-> Async Foreign Scan on async_p1 async_pt_1 (actual rows=0 loops=1)
12127-
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=0 loops=1)
12128-
-> Seq Scan on async_p3 async_pt_3 (actual rows=0 loops=1)
12123+
QUERY PLAN
12124+
----------------------------------------------------------------------------
12125+
Append (actual rows=0.00 loops=1)
12126+
-> Async Foreign Scan on async_p1 async_pt_1 (actual rows=0.00 loops=1)
12127+
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=0.00 loops=1)
12128+
-> Seq Scan on async_p3 async_pt_3 (actual rows=0.00 loops=1)
1212912129
(4 rows)
1213012130

1213112131
-- Clean up

‎doc/src/sgml/auto-explain.sgml

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -337,13 +337,13 @@ LOG: duration: 3.651 ms plan:
337337
Query Text: SELECT count(*)
338338
FROM pg_class, pg_index
339339
WHERE oid = indrelid AND indisunique;
340-
Aggregate (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1 loops=1)
341-
-> Hash Join (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92 loops=1)
340+
Aggregate (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1.00 loops=1)
341+
-> Hash Join (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92.00 loops=1)
342342
Hash Cond: (pg_class.oid = pg_index.indrelid)
343-
-> Seq Scan on pg_class (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255 loops=1)
344-
-> Hash (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92 loops=1)
343+
-> 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)
344+
-> Hash (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92.00 loops=1)
345345
Buckets: 1024 Batches: 1 Memory Usage: 4kB
346-
-> Seq Scan on pg_index (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92 loops=1)
346+
-> 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)
347347
Filter: indisunique
348348
]]></screen>
349349
</sect2>

‎doc/src/sgml/bloom.sgml

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -118,7 +118,7 @@ SELECT 10000000
118118
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
119119
QUERY PLAN
120120
-------------------------------------------------------------------&zwsp;-----------------------------------
121-
Seq Scan on tbloom (cost=0.00..213744.00 rows=250 width=24) (actual time=357.059..357.059 rows=0 loops=1)
121+
Seq Scan on tbloom (cost=0.00..213744.00 rows=250 width=24) (actual time=357.059..357.059 rows=0.00 loops=1)
122122
Filter: ((i2 = 898732) AND (i5 = 123451))
123123
Rows Removed by Filter: 10000000
124124
Buffers: shared hit=63744
@@ -142,7 +142,7 @@ CREATE INDEX
142142
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
143143
QUERY PLAN
144144
-------------------------------------------------------------------&zwsp;-----------------------------------
145-
Seq Scan on tbloom (cost=0.00..213744.00 rows=2 width=24) (actual time=351.016..351.017 rows=0 loops=1)
145+
Seq Scan on tbloom (cost=0.00..213744.00 rows=2 width=24) (actual time=351.016..351.017 rows=0.00 loops=1)
146146
Filter: ((i2 = 898732) AND (i5 = 123451))
147147
Rows Removed by Filter: 10000000
148148
Buffers: shared hit=63744
@@ -166,12 +166,12 @@ CREATE INDEX
166166
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
167167
QUERY PLAN
168168
-------------------------------------------------------------------&zwsp;--------------------------------------------------
169-
Bitmap Heap Scan on tbloom (cost=1792.00..1799.69 rows=2 width=24) (actual time=22.605..22.606 rows=0 loops=1)
169+
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)
170170
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
171171
Rows Removed by Index Recheck: 2300
172172
Heap Blocks: exact=2256
173173
Buffers: shared hit=21864
174-
-&gt; Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=1 width=0) (actual time=20.005..20.005 rows=2300 loops=1)
174+
-&gt; 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)
175175
Index Cond: ((i2 = 898732) AND (i5 = 123451))
176176
Buffers: shared hit=19608
177177
Planning Time: 0.099 ms
@@ -201,15 +201,15 @@ CREATE INDEX
201201
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
202202
QUERY PLAN
203203
-------------------------------------------------------------------&zwsp;--------------------------------------------------------
204-
Bitmap Heap Scan on tbloom (cost=9.29..13.30 rows=1 width=24) (actual time=0.032..0.033 rows=0 loops=1)
204+
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)
205205
Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
206206
Buffers: shared read=6
207-
-&gt; BitmapAnd (cost=9.29..9.29 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1)
207+
-&gt; BitmapAnd (cost=9.29..9.29 rows=1 width=0) (actual time=0.047..0.047 rows=0.00 loops=1)
208208
Buffers: shared hit=6
209-
-&gt; Bitmap Index Scan on btreeidx5 (cost=0.00..4.52 rows=11 width=0) (actual time=0.026..0.026 rows=7 loops=1)
209+
-&gt; 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)
210210
Index Cond: (i5 = 123451)
211211
Buffers: shared hit=3
212-
-&gt; Bitmap Index Scan on btreeidx2 (cost=0.00..4.52 rows=11 width=0) (actual time=0.007..0.007 rows=8 loops=1)
212+
-&gt; 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)
213213
Index Cond: (i2 = 898732)
214214
Buffers: shared hit=3
215215
Planning Time: 0.264 ms

‎doc/src/sgml/jit.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -148,9 +148,9 @@
148148
=# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class;
149149
QUERY PLAN
150150
-------------------------------------------------------------------&zwsp;------------------------------------------
151-
Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.303..0.303 rows=1 loops=1)
151+
Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.303..0.303 rows=1.00 loops=1)
152152
Buffers: shared hit=14
153-
-> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.017..0.111 rows=356 loops=1)
153+
-> 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)
154154
Buffers: shared hit=14
155155
Planning Time: 0.116 ms
156156
Execution Time: 0.365 ms
@@ -165,9 +165,9 @@ SET
165165
=# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class;
166166
QUERY PLAN
167167
-------------------------------------------------------------------&zwsp;------------------------------------------
168-
Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1 loops=1)
168+
Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1.00 loops=1)
169169
Buffers: shared hit=14
170-
-> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.019..0.052 rows=356 loops=1)
170+
-> 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)
171171
Buffers: shared hit=14
172172
Planning Time: 0.133 ms
173173
JIT:

0 commit comments

Comments
 (0)
0