Greetings, On 2010-01-15 11:37 PM +200, Marko Tiikkaja wrote: > On 2010-11-18 5:45 PM +0200, Marko Tiikkaja wrote: > > Here's a patch for showing in EXPLAIN ANALYZE the number of rows a plan > > qual filtered from a node's input. > > Rebased against master.
This patch looked good, in general, to me. I added a few documentation
updates and a comment, but it's a very straight-forward patch as far as
I can tell. Passes all regressions and my additional testing.
commit fac899f7967ce74e14a90af9ca24e1a1f5a580e7
Author: Stephen Frost <sfr...@snowman.net>
Date: Wed Jan 19 22:14:54 2011 -0500
Fix < & > in docs to be < >, as required.
commit 5fcdb75a646912b8b273703caf33dadb80122e1c
Author: Stephen Frost <sfr...@snowman.net>
Date: Wed Jan 19 22:05:05 2011 -0500
Update documentation for EXPLAIN ANALYZE/nfiltered
This patch updates some documentation around EXPLAIN ANALYZE, whose
output has been changed by the patch which added nfiltered to it.
Also added a comment in the only place that seemed to need one.
commit 9ebb0108a217c2d3b7f815d1d902d6bdcc276104
Author: Stephen Frost <sfr...@snowman.net>
Date: Wed Jan 19 21:33:28 2011 -0500
Add nfiltered in EXPLAIN ANALYZE
This patch add the number of rows a plan qual filtered from a node's
input to the EXPLAIN ANALYZE output.
Patch by: Marko Tiikkaja
Thanks,
Stephen
*** a/doc/src/sgml/ref/explain.sgml
--- b/doc/src/sgml/ref/explain.sgml
***************
*** 335,348 **** EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
function:
<programlisting>
! EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
!
! QUERY PLAN
! ---------------------------------------------------------------------
! Aggregate (cost=23.93..23.93 rows=1 width=4)
! -> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
! Index Cond: (i < 10)
! (3 rows)
</programlisting>
</para>
--- 335,352 ----
function:
<programlisting>
! CREATE TABLE test (id integer primary key, bar integer, foo integer);
!
! EXPLAIN SELECT sum(foo) FROM test WHERE id < 10;
!
! QUERY PLAN
! --------------------------------------------------------------------------------
! Aggregate (cost=28.97..28.98 rows=1 width=4)
! -> Bitmap Heap Scan on test (cost=9.26..27.35 rows=647 width=4)
! Recheck Cond: (id < 10)
! -> Bitmap Index Scan on test_pkey (cost=0.00..9.10 rows=647 width=0)
! Index Cond: (id < 10)
! (5 rows)
</programlisting>
</para>
***************
*** 351,369 **** EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
display the execution plan for a prepared query:
<programlisting>
PREPARE query(int, int) AS SELECT sum(bar) FROM test
WHERE id > $1 AND id < $2
GROUP BY foo;
EXPLAIN ANALYZE EXECUTE query(100, 200);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------------
! HashAggregate (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
! -> Index Scan using test_pkey on test (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
! Index Cond: ((id > $1) AND (id < $2))
! Total runtime: 0.851 ms
! (4 rows)
</programlisting>
</para>
--- 355,377 ----
display the execution plan for a prepared query:
<programlisting>
+ CREATE TABLE test (id integer primary key, bar integer, foo integer);
+
PREPARE query(int, int) AS SELECT sum(bar) FROM test
WHERE id > $1 AND id < $2
GROUP BY foo;
EXPLAIN ANALYZE EXECUTE query(100, 200);
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------------
! HashAggregate (cost=14.98..15.01 rows=2 width=8) (actual time=0.045..0.045 rows=0 filtered=0 loops=1)
! -> Bitmap Heap Scan on test (cost=4.35..14.93 rows=10 width=8) (actual time=0.041..0.041 rows=0 filtered=0 loops=1)
! Recheck Cond: ((id > $1) AND (id < $2))
! -> Bitmap Index Scan on test_pkey (cost=0.00..4.35 rows=10 width=0) (actual time=0.035..0.035 rows=0 filtered=0 loops=1)
! Index Cond: ((id > $1) AND (id < $2))
! Total runtime: 0.118 ms
! (6 rows)
</programlisting>
</para>
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 975,992 **** ExplainNode(PlanState *planstate, List *ancestors,
double startup_sec = 1000.0 * planstate->instrument->startup / nloops;
double total_sec = 1000.0 * planstate->instrument->total / nloops;
double rows = planstate->instrument->ntuples / nloops;
if (es->format == EXPLAIN_FORMAT_TEXT)
{
appendStringInfo(es->str,
! " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
! startup_sec, total_sec, rows, nloops);
}
else
{
ExplainPropertyFloat("Actual Startup Time", startup_sec, 3, es);
ExplainPropertyFloat("Actual Total Time", total_sec, 3, es);
ExplainPropertyFloat("Actual Rows", rows, 0, es);
ExplainPropertyFloat("Actual Loops", nloops, 0, es);
}
}
--- 975,994 ----
double startup_sec = 1000.0 * planstate->instrument->startup / nloops;
double total_sec = 1000.0 * planstate->instrument->total / nloops;
double rows = planstate->instrument->ntuples / nloops;
+ double filtered = planstate->instrument->nfiltered / nloops;
if (es->format == EXPLAIN_FORMAT_TEXT)
{
appendStringInfo(es->str,
! " (actual time=%.3f..%.3f rows=%.0f filtered=%.0f loops=%.0f)",
! startup_sec, total_sec, rows, filtered, nloops);
}
else
{
ExplainPropertyFloat("Actual Startup Time", startup_sec, 3, es);
ExplainPropertyFloat("Actual Total Time", total_sec, 3, es);
ExplainPropertyFloat("Actual Rows", rows, 0, es);
+ ExplainPropertyFloat("Rows Filtered", rows, 0, es);
ExplainPropertyFloat("Actual Loops", nloops, 0, es);
}
}
***************
*** 999,1004 **** ExplainNode(PlanState *planstate, List *ancestors,
--- 1001,1007 ----
ExplainPropertyFloat("Actual Startup Time", 0.0, 3, es);
ExplainPropertyFloat("Actual Total Time", 0.0, 3, es);
ExplainPropertyFloat("Actual Rows", 0.0, 0, es);
+ ExplainPropertyFloat("Rows Filtered", 0.0, 0, es);
ExplainPropertyFloat("Actual Loops", 0.0, 0, es);
}
}
*** a/src/backend/executor/execScan.c
--- b/src/backend/executor/execScan.c
***************
*** 19,24 ****
--- 19,25 ----
#include "postgres.h"
#include "executor/executor.h"
+ #include "executor/instrument.h"
#include "miscadmin.h"
#include "utils/memutils.h"
***************
*** 221,226 **** ExecScan(ScanState *node,
--- 222,234 ----
* Tuple fails qual, so free per-tuple memory and try again.
*/
ResetExprContext(econtext);
+
+ /*
+ * Increment our counter for number of filtered-out tuples, if we were
+ * asked to instrument this query.
+ */
+ if (node->ps.instrument)
+ node->ps.instrument->nfiltered += 1;
}
}
*** a/src/include/executor/instrument.h
--- b/src/include/executor/instrument.h
***************
*** 49,54 **** typedef struct Instrumentation
--- 49,55 ----
double startup; /* Total startup time (in seconds) */
double total; /* Total total time (in seconds) */
double ntuples; /* Total tuples produced */
+ double nfiltered; /* Total tuples filtered by qual */
double nloops; /* # of run cycles for this node */
BufferUsage bufusage; /* Total buffer usage */
} Instrumentation;
signature.asc
Description: Digital signature
