Hi,
As specified in $subject, if the bitmap constructed by bitmap index
scan is non-lossy i.e. row-level bitmap, then showing "Recheck Cond"
in EXPLAIN ANALYZE output is pointless. However in EXPLAIN without
ANALYZE we can't say the bitmap is actually a non-lossy one, as we
don't actually construct the "original" bitmap, so showing "Recheck
Cond" in this case makes sense.
Attaching a small patch that corrects EXPLAIN ANALYZE output for bitmap scans.
Note: $subject is identified in [1].
Thoughts?
[1] - https://www.youtube.com/watch?v=UXKYAZOWDgk ---> at 13:50 (mm:ss)
With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com
From 1e407c06689ab408247be7453eeb5778bd92df56 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddy@enterprisedb.com>
Date: Sun, 23 Aug 2020 22:19:03 -0700
Subject: [PATCH v1] Avoid displaying unnecessary Recheck Cond when there are
no lossy pages in bitmap. We do this in only in explain analyze where the
bitmap construction really happens and lossy-ness is determined correctly, as
opposed to a guess work done in non-analyze explains.
---
src/backend/commands/explain.c | 11 +++++-
src/test/regress/expected/partition_prune.out | 35 +++----------------
2 files changed, 14 insertions(+), 32 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index c98c9b5547..071f4eaa8e 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1724,7 +1724,16 @@ ExplainNode(PlanState *planstate, List *ancestors,
"Index Cond", planstate, ancestors, es);
break;
case T_BitmapHeapScan:
- show_scan_qual(((BitmapHeapScan *) plan)->bitmapqualorig,
+ /*
+ * Avoid displaying unnecessary Recheck Cond when there are no
+ * lossy pages in bitmap. We do this in only in explain analyze
+ * where the bitmap construction really happens and lossy-ness
+ * is determined correctly, as opposed to a guess work done in
+ * non-analyze explains.
+ */
+ if (!(es->analyze &&
+ ((BitmapHeapScanState *) planstate)->lossy_pages == 0))
+ show_scan_qual(((BitmapHeapScan *) plan)->bitmapqualorig,
"Recheck Cond", planstate, ancestors, es);
if (((BitmapHeapScan *) plan)->bitmapqualorig)
show_instrumentation_count("Rows Removed by Index Recheck", 2,
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 50d2a7e4b9..e4b560d22d 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2242,51 +2242,42 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1
-> Aggregate (actual rows=1 loops=1)
-> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0 loops=1)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed)
- Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
Index Cond: (a = $0)
-(52 rows)
+(43 rows)
-- Test run-time partition pruning with UNION ALL parents
explain (analyze, costs off, summary off, timing off)
@@ -2298,17 +2289,14 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
- Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
- Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = 1)
@@ -2330,7 +2318,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
Filter: (b = $0)
-> Seq Scan on ab_a3_b3 ab_9 (never executed)
Filter: (b = $0)
-(37 rows)
+(34 rows)
-- A case containing a UNION ALL with a non-partitioned child.
explain (analyze, costs off, summary off, timing off)
@@ -2342,17 +2330,14 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
- Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
- Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = 1)
@@ -2376,7 +2361,7 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s
Filter: (b = $0)
-> Seq Scan on ab_a3_b3 ab_9 (never executed)
Filter: (b = $0)
-(39 rows)
+(36 rows)
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
create table xy_1 (x int, y int);
@@ -2444,65 +2429,53 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Materialize (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Nested Loop (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Materialize (actual rows=1 loops=1)
-> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Materialize (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-(65 rows)
+(53 rows)
table ab;
a | b
--
2.25.1
