Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc2a4078

Browse files
committed
Enable BUFFERS with EXPLAIN ANALYZE by default
The topic of turning EXPLAIN's BUFFERS option on with the ANALYZE optionhas come up a few times over the past few years. In many ways, doing thisseems like a good idea as it may be more obvious to users why a givenquery is running more slowly than they might expect. Also, from my own(David's) personal experience, I've seen users posting to the mailinglists with two identical plans, one slow and one fast asking why theirquery is sometimes slow. In many cases, this is due to additional reads.Having BUFFERS on by default may help reduce some of these questions, andif not, make it more obvious to the user before they post, or save around-trip to the mailing list when additional I/O effort is the cause ofthe slowness.The general consensus is that we want BUFFERS on by default withANALYZE. However, there were more than zero concerns raised with doingso. The primary reason against is the additional verbosity, making itharder to read large plans. Another concern was that buffer informationisn't always useful so may not make sense to have it on by default.It's currently December, so let's commit this to see if anyone comesforward with a strong objection against making this change. We have overhalf a year remaining in the v18 cycle where we could still easily considerreverting this if someone were to come forward with a convincing enoughreason as to why doing this is a bad idea.There were two patches independently submitted to achieve this goal, oneby me and the other by Guillaume. This commit is a mix of both of thesepatches with some additional work done by me to adjust variousadditional places in the documentation which include EXPLAIN ANALYZEoutput.Author: Guillaume Lelarge, David RowleyReviewed-by: Robert Haas, Greg Sabino Mullane, Michael ChristofidesDiscussion:https://postgr.es/m/CANNMO++W7MM8T0KyXN3ZheXXt-uLVM3aEtZd+WNfZ=obxffUiA@mail.gmail.com
1 parent0f57382 commitc2a4078

30 files changed

+257
-197
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11553,7 +11553,7 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c
1155311553
Filter: (async_pt_3.a = local_tbl.a)
1155411554
(15 rows)
1155511555

11556-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
11556+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1155711557
SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar';
1155811558
QUERY PLAN
1155911559
-------------------------------------------------------------------------------
@@ -11799,7 +11799,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W
1179911799
Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000))
1180011800
(20 rows)
1180111801

11802-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
11802+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1180311803
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;
1180411804
QUERY PLAN
1180511805
-----------------------------------------------------------------------------------------
@@ -11843,7 +11843,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
1184311843
Filter: (t1_3.b === 505)
1184411844
(14 rows)
1184511845

11846-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
11846+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1184711847
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
1184811848
QUERY PLAN
1184911849
-------------------------------------------------------------------------
@@ -12003,7 +12003,7 @@ DELETE FROM async_pt WHERE b = 0 RETURNING *;
1200312003
DELETE FROM async_p1;
1200412004
DELETE FROM async_p2;
1200512005
DELETE FROM async_p3;
12006-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
12006+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
1200712007
SELECT * FROM async_pt;
1200812008
QUERY PLAN
1200912009
-------------------------------------------------------------------------

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3904,7 +3904,7 @@ ALTER FOREIGN TABLE async_p2 OPTIONS (use_remote_estimate 'true');
39043904

39053905
EXPLAIN (VERBOSE, COSTS OFF)
39063906
SELECT*FROM local_tbl, async_ptWHERElocal_tbl.a=async_pt.aANDlocal_tbl.c='bar';
3907-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
3907+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
39083908
SELECT*FROM local_tbl, async_ptWHERElocal_tbl.a=async_pt.aANDlocal_tbl.c='bar';
39093909
SELECT*FROM local_tbl, async_ptWHERElocal_tbl.a=async_pt.aANDlocal_tbl.c='bar';
39103910

@@ -3979,13 +3979,13 @@ ANALYZE local_tbl;
39793979

39803980
EXPLAIN (VERBOSE, COSTS OFF)
39813981
SELECT*FROM local_tbl t1LEFT JOIN (SELECT*, (SELECTcount(*)FROM async_ptWHERE a<3000)FROM async_ptWHERE a<3000) t2ONt1.a=t2.a;
3982-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
3982+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
39833983
SELECT*FROM local_tbl t1LEFT JOIN (SELECT*, (SELECTcount(*)FROM async_ptWHERE a<3000)FROM async_ptWHERE a<3000) t2ONt1.a=t2.a;
39843984
SELECT*FROM local_tbl t1LEFT JOIN (SELECT*, (SELECTcount(*)FROM async_ptWHERE a<3000)FROM async_ptWHERE a<3000) t2ONt1.a=t2.a;
39853985

39863986
EXPLAIN (VERBOSE, COSTS OFF)
39873987
SELECT*FROM async_pt t1WHEREt1.b===505LIMIT1;
3988-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
3988+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
39893989
SELECT*FROM async_pt t1WHEREt1.b===505LIMIT1;
39903990
SELECT*FROM async_pt t1WHEREt1.b===505LIMIT1;
39913991

@@ -4037,7 +4037,7 @@ DELETE FROM async_p1;
40374037
DELETEFROM async_p2;
40384038
DELETEFROM async_p3;
40394039

4040-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
4040+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
40414041
SELECT*FROM async_pt;
40424042

40434043
-- Clean up

‎doc/src/sgml/bloom.sgml

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -121,9 +121,10 @@ SELECT 10000000
121121
Seq Scan on tbloom (cost=0.00..213744.00 rows=250 width=24) (actual time=357.059..357.059 rows=0 loops=1)
122122
Filter: ((i2 = 898732) AND (i5 = 123451))
123123
Rows Removed by Filter: 10000000
124+
Buffers: shared hit=63744
124125
Planning Time: 0.346 ms
125126
Execution Time: 357.076 ms
126-
(5 rows)
127+
(6 rows)
127128
</programlisting>
128129
</para>
129130

@@ -144,9 +145,10 @@ CREATE INDEX
144145
Seq Scan on tbloom (cost=0.00..213744.00 rows=2 width=24) (actual time=351.016..351.017 rows=0 loops=1)
145146
Filter: ((i2 = 898732) AND (i5 = 123451))
146147
Rows Removed by Filter: 10000000
148+
Buffers: shared hit=63744
147149
Planning Time: 0.138 ms
148150
Execution Time: 351.035 ms
149-
(5 rows)
151+
(6 rows)
150152
</programlisting>
151153
</para>
152154

@@ -168,11 +170,13 @@ CREATE INDEX
168170
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
169171
Rows Removed by Index Recheck: 2300
170172
Heap Blocks: exact=2256
173+
Buffers: shared hit=21864
171174
-&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)
172175
Index Cond: ((i2 = 898732) AND (i5 = 123451))
176+
Buffers: shared hit=19608
173177
Planning Time: 0.099 ms
174178
Execution Time: 22.632 ms
175-
(8 rows)
179+
(10 rows)
176180
</programlisting>
177181
</para>
178182

@@ -199,14 +203,18 @@ CREATE INDEX
199203
-------------------------------------------------------------------&zwsp;--------------------------------------------------------
200204
Bitmap Heap Scan on tbloom (cost=9.29..13.30 rows=1 width=24) (actual time=0.032..0.033 rows=0 loops=1)
201205
Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
206+
Buffers: shared read=6
202207
-&gt; BitmapAnd (cost=9.29..9.29 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1)
208+
Buffers: shared hit=6
203209
-&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)
204210
Index Cond: (i5 = 123451)
211+
Buffers: shared hit=3
205212
-&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)
206213
Index Cond: (i2 = 898732)
214+
Buffers: shared hit=3
207215
Planning Time: 0.264 ms
208216
Execution Time: 0.047 ms
209-
(9 rows)
217+
(13 rows)
210218
</programlisting>
211219
Although this query runs much faster than with either of the single
212220
indexes, we pay a penalty in index size. Each of the single-column

‎doc/src/sgml/jit.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -149,10 +149,11 @@
149149
QUERY PLAN
150150
-------------------------------------------------------------------&zwsp;------------------------------------------
151151
Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.303..0.303 rows=1 loops=1)
152+
Buffers: shared hit=14
152153
-> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.017..0.111 rows=356 loops=1)
154+
Buffers: shared hit=14
153155
Planning Time: 0.116 ms
154156
Execution Time: 0.365 ms
155-
(4 rows)
156157
</screen>
157158
Given the cost of the plan, it is entirely reasonable that no
158159
<acronym>JIT</acronym> was used; the cost of <acronym>JIT</acronym> would
@@ -165,7 +166,9 @@ SET
165166
QUERY PLAN
166167
-------------------------------------------------------------------&zwsp;------------------------------------------
167168
Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1 loops=1)
169+
Buffers: shared hit=14
168170
-> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.019..0.052 rows=356 loops=1)
171+
Buffers: shared hit=14
169172
Planning Time: 0.133 ms
170173
JIT:
171174
Functions: 3

‎doc/src/sgml/perform.sgml

Lines changed: 31 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -39,7 +39,7 @@
3939

4040
<para>
4141
Examples in this section are drawn from the regression test database
42-
after doing a <command>VACUUM ANALYZE</command>, usingv17 development sources.
42+
after doing a <command>VACUUM ANALYZE</command>, usingv18 development sources.
4343
You should be able to get similar results if you try the examples
4444
yourself, but your estimated costs and row counts might vary slightly
4545
because <command>ANALYZE</command>'s statistics are random samples rather
@@ -722,13 +722,19 @@ WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
722722
QUERY PLAN
723723
-------------------------------------------------------------------&zwsp;--------------------------------------------------------------
724724
Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1)
725+
Buffers: shared hit=36 read=6
725726
-&gt; 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)
726727
Recheck Cond: (unique1 &lt; 10)
727728
Heap Blocks: exact=10
729+
Buffers: shared hit=3 read=5 written=4
728730
-&gt; 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)
729731
Index Cond: (unique1 &lt; 10)
732+
Buffers: shared hit=2
730733
-&gt; 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)
731734
Index Cond: (unique2 = t1.unique2)
735+
Buffers: shared hit=24 read=6
736+
Planning:
737+
Buffers: shared hit=15 dirtied=9
732738
Planning Time: 0.485 ms
733739
Execution Time: 0.073 ms
734740
</screen>
@@ -769,16 +775,24 @@ WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
769775
Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1)
770776
Sort Key: t1.fivethous
771777
Sort Method: quicksort Memory: 74kB
778+
Buffers: shared hit=440
772779
-&gt; Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1)
773780
Hash Cond: (t2.unique2 = t1.unique2)
781+
Buffers: shared hit=437
774782
-&gt; Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1)
783+
Buffers: shared hit=345
775784
-&gt; Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1)
776785
Buckets: 1024 Batches: 1 Memory Usage: 35kB
786+
Buffers: shared hit=92
777787
-&gt; 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)
778788
Recheck Cond: (unique1 &lt; 100)
779789
Heap Blocks: exact=90
790+
Buffers: shared hit=92
780791
-&gt; 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)
781792
Index Cond: (unique1 &lt; 100)
793+
Buffers: shared hit=2
794+
Planning:
795+
Buffers: shared hit=12
782796
Planning Time: 0.187 ms
783797
Execution Time: 3.036 ms
784798
</screen>
@@ -803,6 +817,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten &lt; 7;
803817
Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1)
804818
Filter: (ten &lt; 7)
805819
Rows Removed by Filter: 3000
820+
Buffers: shared hit=345
806821
Planning Time: 0.102 ms
807822
Execution Time: 2.145 ms
808823
</screen>
@@ -826,6 +841,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
826841
Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1)
827842
Filter: (f1 @&gt; '((0.5,2))'::polygon)
828843
Rows Removed by Filter: 7
844+
Buffers: shared hit=1
829845
Planning Time: 0.039 ms
830846
Execution Time: 0.033 ms
831847
</screen>
@@ -845,6 +861,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
845861
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)
846862
Index Cond: (f1 @&gt; '((0.5,2))'::polygon)
847863
Rows Removed by Index Recheck: 1
864+
Buffers: shared hit=1
848865
Planning Time: 0.039 ms
849866
Execution Time: 0.098 ms
850867
</screen>
@@ -857,34 +874,31 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
857874
</para>
858875

859876
<para>
860-
<command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with
861-
<literal>ANALYZE</literal> to get even more run time statistics:
877+
<command>EXPLAIN</command> has a <literal>BUFFERS</literal> option which
878+
provides additional detail about I/O operations performed during the
879+
planning and execution of the given query. The buffer numbers displayed
880+
show the count of the non-distinct buffers hit, read, dirtied, and written
881+
for the given node and all of its child nodes. The
882+
<literal>ANALYZE</literal> option implicitly enables the
883+
<literal>BUFFERS</literal> option. If this
884+
is undesired, <literal>BUFFERS</literal> may be explicitly disabled:
862885

863886
<screen>
864-
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000;
887+
EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000;
865888

866889
QUERY PLAN
867890
-------------------------------------------------------------------&zwsp;--------------------------------------------------------------
868891
Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1)
869892
Recheck Cond: ((unique1 &lt; 100) AND (unique2 &gt; 9000))
870893
Heap Blocks: exact=10
871-
Buffers: shared hit=14 read=3
872894
-&gt; BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1)
873-
Buffers: shared hit=4 read=3
874895
-&gt; 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)
875896
Index Cond: (unique1 &lt; 100)
876-
Buffers: shared hit=2
877897
-&gt; 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)
878898
Index Cond: (unique2 &gt; 9000)
879-
Buffers: shared hit=2 read=3
880-
Planning:
881-
Buffers: shared hit=3
882899
Planning Time: 0.162 ms
883900
Execution Time: 0.143 ms
884901
</screen>
885-
886-
The numbers provided by <literal>BUFFERS</literal> help to identify which parts
887-
of the query are the most I/O-intensive.
888902
</para>
889903

890904
<para>
@@ -906,8 +920,10 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 &lt; 100;
906920
-&gt; Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1)
907921
Recheck Cond: (unique1 &lt; 100)
908922
Heap Blocks: exact=90
923+
Buffers: shared hit=4 read=2
909924
-&gt; 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)
910925
Index Cond: (unique1 &lt; 100)
926+
Buffers: shared read=2
911927
Planning Time: 0.151 ms
912928
Execution Time: 1.856 ms
913929

@@ -1040,10 +1056,12 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000
10401056
QUERY PLAN
10411057
-------------------------------------------------------------------&zwsp;------------------------------------------------------------
10421058
Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1)
1059+
Buffers: shared hit=16
10431060
-&gt; 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)
10441061
Index Cond: (unique2 &gt; 9000)
10451062
Filter: (unique1 &lt; 100)
10461063
Rows Removed by Filter: 287
1064+
Buffers: shared hit=16
10471065
Planning Time: 0.077 ms
10481066
Execution Time: 0.086 ms
10491067
</screen>

‎doc/src/sgml/planstats.sgml

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -489,7 +489,7 @@ SELECT relpages, reltuples FROM pg_class WHERE relname = 't';
489489
condition on the <structfield>a</structfield> column:
490490

491491
<programlisting>
492-
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1;
492+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1;
493493
QUERY PLAN
494494
-------------------------------------------------------------------&zwsp;------------
495495
Seq Scan on t (cost=0.00..170.00 rows=100 width=8) (actual rows=100 loops=1)
@@ -506,7 +506,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1;
506506
condition on both columns, combining them with <literal>AND</literal>:
507507

508508
<programlisting>
509-
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
509+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
510510
QUERY PLAN
511511
-------------------------------------------------------------------&zwsp;----------
512512
Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1)
@@ -530,7 +530,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
530530
<programlisting>
531531
CREATE STATISTICS stts (dependencies) ON a, b FROM t;
532532
ANALYZE t;
533-
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
533+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
534534
QUERY PLAN
535535
-------------------------------------------------------------------&zwsp;------------
536536
Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1)
@@ -551,7 +551,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
551551
estimated number of rows returned by the HashAggregate node) is very
552552
accurate:
553553
<programlisting>
554-
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a;
554+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a;
555555
QUERY PLAN
556556
-------------------------------------------------------------------&zwsp;----------------------
557557
HashAggregate (cost=195.00..196.00 rows=100 width=12) (actual rows=100 loops=1)
@@ -562,7 +562,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a;
562562
groups in a query with two columns in <command>GROUP BY</command>, as
563563
in the following example, is off by an order of magnitude:
564564
<programlisting>
565-
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
565+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
566566
QUERY PLAN
567567
-------------------------------------------------------------------&zwsp;-------------------------
568568
HashAggregate (cost=220.00..230.00 rows=1000 width=16) (actual rows=100 loops=1)
@@ -575,7 +575,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
575575
DROP STATISTICS stts;
576576
CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
577577
ANALYZE t;
578-
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
578+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
579579
QUERY PLAN
580580
-------------------------------------------------------------------&zwsp;-------------------------
581581
HashAggregate (cost=220.00..221.00 rows=100 width=16) (actual rows=100 loops=1)
@@ -615,7 +615,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
615615
DROP STATISTICS stts;
616616
CREATE STATISTICS stts2 (mcv) ON a, b FROM t;
617617
ANALYZE t;
618-
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
618+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
619619
QUERY PLAN
620620
-------------------------------------------------------------------&zwsp;------------
621621
Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1)
@@ -672,7 +672,7 @@ SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
672672
to decide which combinations are compatible.
673673

674674
<programlisting>
675-
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10;
675+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 10;
676676
QUERY PLAN
677677
-------------------------------------------------------------------&zwsp;--------
678678
Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1)
@@ -685,7 +685,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10;
685685
consider the following range query for the same table:
686686

687687
<programlisting>
688-
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a &lt;= 49 AND b &gt; 49;
688+
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a &lt;= 49 AND b &gt; 49;
689689
QUERY PLAN
690690
-------------------------------------------------------------------&zwsp;--------
691691
Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp