From: Justin Pryzby Date: Tue, 16 Nov 2021 11:22:40 -0600 Subject: [PATCH 5/6] f!Rows removed by filter This cleans one more kludge in partition_prune, but drags in 2 more files... --- .../postgres_fdw/expected/postgres_fdw.out | 6 ++-- src/backend/commands/explain.c | 36 +++++++++---------- src/test/regress/expected/memoize.out | 9 ++--- src/test/regress/expected/partition_prune.out | 29 +++++---------- src/test/regress/expected/select_parallel.out | 4 +-- src/test/regress/sql/partition_prune.sql | 1 - 6 files changed, 32 insertions(+), 53 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f210f911880..1d03796c543 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10496,13 +10496,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Nested Loop (actual rows=1 loops=1) -> Seq Scan on local_tbl (actual rows=1 loops=1) Filter: (c = 'bar'::text) - Rows Removed by Filter: 1 -> Append (actual rows=1 loops=1) -> Async Foreign Scan on async_p1 async_pt_1 (never executed) -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1) -> Seq Scan on async_p3 async_pt_3 (never executed) Filter: (local_tbl.a = a) -(9 rows) +(8 rows) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; a | b | c | a | b | c @@ -10636,8 +10635,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (b === 505) -> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1) Filter: (b === 505) - Rows Removed by Filter: 101 -(9 rows) +(8 rows) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; a | b | c diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index f72f9fabf8b..1e02a5f81d9 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1774,7 +1774,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexScan *) plan)->indexorderbyorig, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1787,7 +1787,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_scan_qual(((IndexOnlyScan *) plan)->indexorderby, "Order By", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze && es->machine) @@ -1805,7 +1805,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Index Recheck", 2, planstate, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze) @@ -1823,7 +1823,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_WorkTableScan: case T_SubqueryScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1832,7 +1832,7 @@ ExplainNode(PlanState *planstate, List *ancestors, Gather *gather = (Gather *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1860,7 +1860,7 @@ ExplainNode(PlanState *planstate, List *ancestors, GatherMerge *gm = (GatherMerge *) plan; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); ExplainPropertyInteger("Workers Planned", NULL, @@ -1898,7 +1898,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1912,7 +1912,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es->verbose, es); } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -1928,7 +1928,7 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_orclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } @@ -1945,14 +1945,14 @@ ExplainNode(PlanState *planstate, List *ancestors, tidquals = list_make1(make_andclause(tidquals)); show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); } break; case T_ForeignScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_foreignscan_info((ForeignScanState *) planstate, es); @@ -1962,7 +1962,7 @@ ExplainNode(PlanState *planstate, List *ancestors, CustomScanState *css = (CustomScanState *) planstate; show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (css->methods->ExplainCustomScan) @@ -1976,7 +1976,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -1989,7 +1989,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2002,7 +2002,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Join Filter", 1, planstate, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); break; @@ -2010,14 +2010,14 @@ ExplainNode(PlanState *planstate, List *ancestors, show_agg_keys(castNode(AggState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); show_hashagg_info((AggState *) planstate, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; case T_Group: show_group_keys(castNode(GroupState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; @@ -2039,7 +2039,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_upper_qual((List *) ((Result *) plan)->resconstantqual, "One-Time Filter", planstate, ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); - if (plan->qual) + if (plan->qual && es->machine) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); break; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 1b1557ce9fc..7f4b73fd42d 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t2.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false); -> Nested Loop (actual rows=1000 loops=N) -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N) Filter: (unique1 < 1000) - Rows Removed by Filter: 9000 -> Memoize (actual rows=1 loops=N) Cache Key: t1.twenty Cache Mode: logical Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N) Index Cond: (unique1 = t1.twenty) -(11 rows) +(10 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -103,14 +101,13 @@ WHERE t2.unique1 < 1200;', true); -> Nested Loop (actual rows=1200 loops=N) -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N) Filter: (unique1 < 1200) - Rows Removed by Filter: 8800 -> Memoize (actual rows=1 loops=N) Cache Key: t2.thousand Cache Mode: logical Hits: N Misses: N Evictions: N Overflows: 0 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.thousand) -(11 rows) +(10 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index cabadd48b81..3576e65bc29 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh Append (actual rows=0 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1) Filter: (a = (list_part_fn(1) + a)) - Rows Removed by Filter: 1 -(13 rows) +(9 rows) rollback; drop table list_part; @@ -1957,7 +1953,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; @@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) delete from lprt_a where a = 1; select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); @@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) Index Cond: (a = a.a) @@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(27 rows) reset enable_hashjoin; reset enable_mergejoin; @@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1); Filter: ((a = $1) AND (b = $0)) -> Seq Scan on xy_1 (actual rows=0 loops=1) Filter: ((x = $1) AND (y = $0)) - Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = $0)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = $0)) -(19 rows) +(18 rows) -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: value - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) Filter: (a = $0) -(9 rows) +(8 rows) explain (analyze, costs off, summary off, timing off) select * from boolp where a = (select value from boolvalues where not value); @@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value); InitPlan 1 (returns $0) -> Seq Scan on boolvalues (actual rows=1 loops=1) Filter: (NOT value) - Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) Filter: (a = $0) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = $0) -(9 rows) +(8 rows) drop table boolp; -- @@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15); Subplans Removed: 1 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) +(7 rows) execute mt_q1(15); a @@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25); Subplans Removed: 2 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) +(5 rows) execute mt_q1(25); a diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 9f36c627419..02176d7e2f6 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off) -> Nested Loop (actual rows=98000 loops=1) -> Seq Scan on tenk2 (actual rows=10 loops=1) Filter: (thousand = 0) - Rows Removed by Filter: 9990 -> Gather (actual rows=9800 loops=10) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) Filter: (hundred > 1) - Rows Removed by Filter: 40 -(11 rows) +(9 rows) alter table tenk2 reset (parallel_workers); reset work_mem; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d70bd8610cb..e3938bea9c0 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -463,7 +463,6 @@ begin loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); - ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); return next ln; end loop; end; -- 2.17.1 --AXo2lOxbfudqq8ta Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0006-f-Workers-Launched.patch"