From 3080e7a9e8c6953410e3ad3ba8eca69b6cc467e5 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Tue, 16 Nov 2021 11:22:40 -0600 Subject: [PATCH 5/7] +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/merge.out | 3 +- src/test/regress/expected/partition_prune.out | 29 +++++---------- src/test/regress/expected/select_parallel.out | 4 +-- src/test/regress/sql/partition_prune.sql | 1 - 7 files changed, 33 insertions(+), 55 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 7bf35602b02..c7e971f32de 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10730,13 +10730,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 @@ -11018,8 +11017,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 e5a08e8e2c1..23e34184acd 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1796,7 +1796,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; @@ -1809,7 +1809,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) @@ -1827,7 +1827,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) @@ -1845,7 +1845,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; @@ -1854,7 +1854,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, @@ -1882,7 +1882,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, @@ -1920,7 +1920,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; @@ -1934,7 +1934,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; @@ -1950,7 +1950,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); } @@ -1967,14 +1967,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); @@ -1984,7 +1984,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) @@ -1998,7 +1998,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; @@ -2011,7 +2011,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; @@ -2024,7 +2024,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; @@ -2032,7 +2032,7 @@ 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; @@ -2047,7 +2047,7 @@ ExplainNode(PlanState *planstate, List *ancestors, 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; @@ -2069,7 +2069,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/merge.out b/src/test/regress/expected/merge.out index f252e7ffb34..8bf08ff4ca4 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1461,8 +1461,7 @@ WHEN MATCHED AND t.a < 10 THEN Buckets: xxx Batches: xxx -> Seq Scan on ex_mtarget t (actual rows=0 loops=1) Filter: (a < '-1000'::integer) - Rows Removed by Filter: 54 -(9 rows) +(8 rows) DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); 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 b285ed5ecb1..aa6e385d84d 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