Skip to content

Commit f197272

Browse files
tglsfdctglsfdc
committed
Make EXPLAIN ANALYZE report the numbers of rows rejected by filter steps.
This provides information about the numbers of tuples that were visited but not returned by table scans, as well as the numbers of join tuples that were considered and discarded within a join plan node. There is still some discussion going on about the best way to report counts for outer-join situations, but I think most of what's in the patch would not change if we revise that, so I'm going to go ahead and commit it as-is. Documentation changes to follow (they weren't in the submitted patch either). Marko Tiikkaja, reviewed by Marc Cousin, somewhat revised by Tom
1 parent 4893552 commit f197272

20 files changed

+144
-20
lines changed

‎src/backend/commands/explain.c

Lines changed: 78 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,6 @@
1818
#include "commands/defrem.h"
1919
#include "commands/prepare.h"
2020
#include "executor/hashjoin.h"
21-
#include "executor/instrument.h"
2221
#include "foreign/fdwapi.h"
2322
#include "optimizer/clauses.h"
2423
#include "parser/parsetree.h"
@@ -76,6 +75,8 @@ static void show_sort_keys_common(PlanState *planstate,
7675
List *ancestors, ExplainState *es);
7776
static void show_sort_info(SortState *sortstate, ExplainState *es);
7877
static void show_hash_info(HashState *hashstate, ExplainState *es);
78+
static void show_instrumentation_count(const char *qlabel, int which,
79+
PlanState *planstate, ExplainState *es);
7980
static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es);
8081
static const char *explain_get_index_name(Oid indexId);
8182
static void ExplainScanTarget(Scan *plan, ExplainState *es);
@@ -1000,9 +1001,15 @@ ExplainNode(PlanState *planstate, List *ancestors,
10001001
case T_IndexScan:
10011002
show_scan_qual(((IndexScan *) plan)->indexqualorig,
10021003
"Index Cond", planstate, ancestors, es);
1004+
if (((IndexScan *) plan)->indexqualorig)
1005+
show_instrumentation_count("Rows Removed by Index Recheck", 2,
1006+
planstate, es);
10031007
show_scan_qual(((IndexScan *) plan)->indexorderbyorig,
10041008
"Order By", planstate, ancestors, es);
10051009
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
1010+
if (plan->qual)
1011+
show_instrumentation_count("Rows Removed by Filter", 1,
1012+
planstate, es);
10061013
break;
10071014
case T_BitmapIndexScan:
10081015
show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig,
@@ -1011,20 +1018,29 @@ ExplainNode(PlanState *planstate, List *ancestors,
10111018
case T_BitmapHeapScan:
10121019
show_scan_qual(((BitmapHeapScan *) plan)->bitmapqualorig,
10131020
"Recheck Cond", planstate, ancestors, es);
1021+
if (((BitmapHeapScan *) plan)->bitmapqualorig)
1022+
show_instrumentation_count("Rows Removed by Index Recheck", 2,
1023+
planstate, es);
10141024
/* FALL THRU */
10151025
case T_SeqScan:
10161026
case T_ValuesScan:
10171027
case T_CteScan:
10181028
case T_WorkTableScan:
10191029
case T_SubqueryScan:
10201030
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
1031+
if (plan->qual)
1032+
show_instrumentation_count("Rows Removed by Filter", 1,
1033+
planstate, es);
10211034
break;
10221035
case T_FunctionScan:
10231036
if (es->verbose)
10241037
show_expression(((FunctionScan *) plan)->funcexpr,
10251038
"Function Call", planstate, ancestors,
10261039
es->verbose, es);
10271040
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
1041+
if (plan->qual)
1042+
show_instrumentation_count("Rows Removed by Filter", 1,
1043+
planstate, es);
10281044
break;
10291045
case T_TidScan:
10301046
{
@@ -1038,34 +1054,61 @@ ExplainNode(PlanState *planstate, List *ancestors,
10381054
tidquals = list_make1(make_orclause(tidquals));
10391055
show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es);
10401056
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
1057+
if (plan->qual)
1058+
show_instrumentation_count("Rows Removed by Filter", 1,
1059+
planstate, es);
10411060
}
10421061
break;
10431062
case T_ForeignScan:
10441063
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
1064+
if (plan->qual)
1065+
show_instrumentation_count("Rows Removed by Filter", 1,
1066+
planstate, es);
10451067
show_foreignscan_info((ForeignScanState *) planstate, es);
10461068
break;
10471069
case T_NestLoop:
10481070
show_upper_qual(((NestLoop *) plan)->join.joinqual,
10491071
"Join Filter", planstate, ancestors, es);
1072+
if (((NestLoop *) plan)->join.joinqual)
1073+
show_instrumentation_count("Rows Removed by Join Filter", 1,
1074+
planstate, es);
10501075
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
1076+
if (plan->qual)
1077+
show_instrumentation_count("Rows Removed by Filter", 2,
1078+
planstate, es);
10511079
break;
10521080
case T_MergeJoin:
10531081
show_upper_qual(((MergeJoin *) plan)->mergeclauses,
10541082
"Merge Cond", planstate, ancestors, es);
10551083
show_upper_qual(((MergeJoin *) plan)->join.joinqual,
10561084
"Join Filter", planstate, ancestors, es);
1085+
if (((MergeJoin *) plan)->join.joinqual)
1086+
show_instrumentation_count("Rows Removed by Join Filter", 1,
1087+
planstate, es);
10571088
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
1089+
if (plan->qual)
1090+
show_instrumentation_count("Rows Removed by Filter", 2,
1091+
planstate, es);
10581092
break;
10591093
case T_HashJoin:
10601094
show_upper_qual(((HashJoin *) plan)->hashclauses,
10611095
"Hash Cond", planstate, ancestors, es);
10621096
show_upper_qual(((HashJoin *) plan)->join.joinqual,
10631097
"Join Filter", planstate, ancestors, es);
1098+
if (((HashJoin *) plan)->join.joinqual)
1099+
show_instrumentation_count("Rows Removed by Join Filter", 1,
1100+
planstate, es);
10641101
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
1102+
if (plan->qual)
1103+
show_instrumentation_count("Rows Removed by Filter", 2,
1104+
planstate, es);
10651105
break;
10661106
case T_Agg:
10671107
case T_Group:
10681108
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
1109+
if (plan->qual)
1110+
show_instrumentation_count("Rows Removed by Filter", 1,
1111+
planstate, es);
10691112
break;
10701113
case T_Sort:
10711114
show_sort_keys((SortState *) planstate, ancestors, es);
@@ -1079,6 +1122,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
10791122
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
10801123
"One-Time Filter", planstate, ancestors, es);
10811124
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
1125+
if (plan->qual)
1126+
show_instrumentation_count("Rows Removed by Filter", 1,
1127+
planstate, es);
10821128
break;
10831129
case T_Hash:
10841130
show_hash_info((HashState *) planstate, es);
@@ -1508,6 +1554,37 @@ show_hash_info(HashState *hashstate, ExplainState *es)
15081554
}
15091555
}
15101556

1557+
/*
1558+
* If it's EXPLAIN ANALYZE, show instrumentation information for a plan node
1559+
*
1560+
* "which" identifies which instrumentation counter to print
1561+
*/
1562+
static void
1563+
show_instrumentation_count(const char *qlabel, int which,
1564+
PlanState *planstate, ExplainState *es)
1565+
{
1566+
double nfiltered;
1567+
double nloops;
1568+
1569+
if (!es->analyze || !planstate->instrument)
1570+
return;
1571+
1572+
if (which == 2)
1573+
nfiltered = planstate->instrument->nfiltered2;
1574+
else
1575+
nfiltered = planstate->instrument->nfiltered1;
1576+
nloops = planstate->instrument->nloops;
1577+
1578+
/* In text mode, suppress zero counts; they're not interesting enough */
1579+
if (nfiltered > 0 || es->format != EXPLAIN_FORMAT_TEXT)
1580+
{
1581+
if (nloops > 0)
1582+
ExplainPropertyFloat(qlabel, nfiltered / nloops, 0, es);
1583+
else
1584+
ExplainPropertyFloat(qlabel, 0.0, 0, es);
1585+
}
1586+
}
1587+
15111588
/*
15121589
* Show extra information for a ForeignScan node.
15131590
*/

‎src/backend/commands/trigger.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,6 @@
2929
#include "commands/defrem.h"
3030
#include "commands/trigger.h"
3131
#include "executor/executor.h"
32-
#include "executor/instrument.h"
3332
#include "miscadmin.h"
3433
#include "nodes/bitmapset.h"
3534
#include "nodes/makefuncs.h"

‎src/backend/executor/execAmi.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,6 @@
1313
#include "postgres.h"
1414

1515
#include "executor/execdebug.h"
16-
#include "executor/instrument.h"
1716
#include "executor/nodeAgg.h"
1817
#include "executor/nodeAppend.h"
1918
#include "executor/nodeBitmapAnd.h"

‎src/backend/executor/execMain.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,6 @@
4747
#include "commands/tablespace.h"
4848
#include "commands/trigger.h"
4949
#include "executor/execdebug.h"
50-
#include "executor/instrument.h"
5150
#include "miscadmin.h"
5251
#include "optimizer/clauses.h"
5352
#include "parser/parse_clause.h"

‎src/backend/executor/execProcnode.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -77,7 +77,6 @@
7777
#include "postgres.h"
7878

7979
#include "executor/executor.h"
80-
#include "executor/instrument.h"
8180
#include "executor/nodeAgg.h"
8281
#include "executor/nodeAppend.h"
8382
#include "executor/nodeBitmapAnd.h"

‎src/backend/executor/execScan.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -219,6 +219,8 @@ ExecScan(ScanState *node,
219219
return slot;
220220
}
221221
}
222+
else
223+
InstrCountFiltered1(node, 1);
222224

223225
/*
224226
* Tuple fails qual, so free per-tuple memory and try again.

‎src/backend/executor/instrument.c

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@ BufferUsage pgBufferUsage;
2222
static void BufferUsageAccumDiff(BufferUsage *dst,
2323
const BufferUsage *add, const BufferUsage *sub);
2424

25+
2526
/* Allocate new instrumentation structure(s) */
2627
Instrumentation *
2728
InstrAlloc(int n, int instrument_options)
@@ -31,13 +32,14 @@ InstrAlloc(int n, int instrument_options)
3132
/* timer is always required for now */
3233
Assert(instrument_options & INSTRUMENT_TIMER);
3334

35+
/* initialize all fields to zeroes, then modify as needed */
3436
instr = palloc0(n * sizeof(Instrumentation));
3537
if (instrument_options & INSTRUMENT_BUFFERS)
3638
{
3739
int i;
3840

3941
for (i = 0; i < n; i++)
40-
instr[i].needs_bufusage = true;
42+
instr[i].need_bufusage = true;
4143
}
4244

4345
return instr;
@@ -52,8 +54,8 @@ InstrStartNode(Instrumentation *instr)
5254
else
5355
elog(DEBUG2, "InstrStartNode called twice in a row");
5456

55-
/* initialize buffer usage per plan node */
56-
if (instr->needs_bufusage)
57+
/* save buffer usage totals at node entry, if needed */
58+
if (instr->need_bufusage)
5759
instr->bufusage_start = pgBufferUsage;
5860
}
5961

@@ -77,8 +79,8 @@ InstrStopNode(Instrumentation *instr, double nTuples)
7779

7880
INSTR_TIME_SET_ZERO(instr->starttime);
7981

80-
/* Adds delta of buffer usage to node's count. */
81-
if (instr->needs_bufusage)
82+
/* Add delta of buffer usage since entry to node's totals */
83+
if (instr->need_bufusage)
8284
BufferUsageAccumDiff(&instr->bufusage,
8385
&pgBufferUsage, &instr->bufusage_start);
8486

@@ -119,12 +121,12 @@ InstrEndLoop(Instrumentation *instr)
119121
instr->tuplecount = 0;
120122
}
121123

124+
/* dst += add - sub */
122125
static void
123126
BufferUsageAccumDiff(BufferUsage *dst,
124127
const BufferUsage *add,
125128
const BufferUsage *sub)
126129
{
127-
/* dst += add - sub */
128130
dst->shared_blks_hit += add->shared_blks_hit - sub->shared_blks_hit;
129131
dst->shared_blks_read += add->shared_blks_read - sub->shared_blks_read;
130132
dst->shared_blks_written += add->shared_blks_written - sub->shared_blks_written;

‎src/backend/executor/nodeAgg.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1204,6 +1204,8 @@ agg_retrieve_direct(AggState *aggstate)
12041204
return result;
12051205
}
12061206
}
1207+
else
1208+
InstrCountFiltered1(aggstate, 1);
12071209
}
12081210

12091211
/* No more groups */
@@ -1354,6 +1356,8 @@ agg_retrieve_hash_table(AggState *aggstate)
13541356
return result;
13551357
}
13561358
}
1359+
else
1360+
InstrCountFiltered1(aggstate, 1);
13571361
}
13581362

13591363
/* No more groups */

‎src/backend/executor/nodeBitmapAnd.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,6 @@
2929
#include "postgres.h"
3030

3131
#include "executor/execdebug.h"
32-
#include "executor/instrument.h"
3332
#include "executor/nodeBitmapAnd.h"
3433

3534

‎src/backend/executor/nodeBitmapHeapscan.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,7 @@ BitmapHeapNext(BitmapHeapScanState *node)
278278
if (!ExecQual(node->bitmapqualorig, econtext, false))
279279
{
280280
/* Fails recheck, so drop it and loop back for another */
281+
InstrCountFiltered2(node, 1);
281282
ExecClearTuple(slot);
282283
continue;
283284
}

‎src/backend/executor/nodeBitmapIndexscan.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,6 @@
2222
#include "postgres.h"
2323

2424
#include "executor/execdebug.h"
25-
#include "executor/instrument.h"
2625
#include "executor/nodeBitmapIndexscan.h"
2726
#include "executor/nodeIndexscan.h"
2827
#include "miscadmin.h"

‎src/backend/executor/nodeBitmapOr.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,6 @@
2929
#include "postgres.h"
3030

3131
#include "executor/execdebug.h"
32-
#include "executor/instrument.h"
3332
#include "executor/nodeBitmapOr.h"
3433
#include "miscadmin.h"
3534

‎src/backend/executor/nodeGroup.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -118,6 +118,8 @@ ExecGroup(GroupState *node)
118118
return result;
119119
}
120120
}
121+
else
122+
InstrCountFiltered1(node, 1);
121123
}
122124

123125
/*
@@ -179,6 +181,8 @@ ExecGroup(GroupState *node)
179181
return result;
180182
}
181183
}
184+
else
185+
InstrCountFiltered1(node, 1);
182186
}
183187

184188
/* NOTREACHED */

‎src/backend/executor/nodeHash.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,6 @@
2828
#include "commands/tablespace.h"
2929
#include "executor/execdebug.h"
3030
#include "executor/hashjoin.h"
31-
#include "executor/instrument.h"
3231
#include "executor/nodeHash.h"
3332
#include "executor/nodeHashjoin.h"
3433
#include "miscadmin.h"

‎src/backend/executor/nodeHashjoin.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -325,7 +325,11 @@ ExecHashJoin(HashJoinState *node)
325325
return result;
326326
}
327327
}
328+
else
329+
InstrCountFiltered2(node, 1);
328330
}
331+
else
332+
InstrCountFiltered1(node, 1);
329333
break;
330334

331335
case HJ_FILL_OUTER_TUPLE:
@@ -360,6 +364,8 @@ ExecHashJoin(HashJoinState *node)
360364
return result;
361365
}
362366
}
367+
else
368+
InstrCountFiltered2(node, 1);
363369
}
364370
break;
365371

@@ -397,6 +403,8 @@ ExecHashJoin(HashJoinState *node)
397403
return result;
398404
}
399405
}
406+
else
407+
InstrCountFiltered2(node, 1);
400408
break;
401409

402410
case HJ_NEED_NEW_BATCH:

‎src/backend/executor/nodeIndexscan.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -96,7 +96,11 @@ IndexNext(IndexScanState *node)
9696
econtext->ecxt_scantuple = slot;
9797
ResetExprContext(econtext);
9898
if (!ExecQual(node->indexqualorig, econtext, false))
99-
continue; /* nope, so ask index for another one */
99+
{
100+
/* Fails recheck, so drop it and loop back for another */
101+
InstrCountFiltered2(node, 1);
102+
continue;
103+
}
100104
}
101105

102106
return slot;

0 commit comments

Comments
 (0)