Hello I have some questions related to the query plan output about the planned and actual rows. In the following example:
# explain (analyze true, costs true, format yaml) select * from users where
lower(city) = 'melanyfort' and lower(state) = 'ohio';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
- Plan:
+
Node Type: "Bitmap Heap Scan"
+
Parallel Aware: false
+
Relation Name: "users"
+
Alias: "users"
+
Startup Cost: 10.78
+
Total Cost: 14.80
+
Plan Rows: 1
+
Plan Width: 73
+
Actual Startup Time: 0.155
+
Actual Total Time: 0.155
+
Actual Rows: 0
+
Actual Loops: 1
+
Recheck Cond: "((lower((city)::text) = 'melanyfort'::text) AND
(lower((state)::text) = 'ohio'::text))"+
Rows Removed by Index Recheck: 0
+
Exact Heap Blocks: 0
+
Lossy Heap Blocks: 0
+
Plans:
+
- Node Type: "BitmapAnd"
+
Parent Relationship: "Outer"
+
Parallel Aware: false
+
Startup Cost: 10.78
+
Total Cost: 10.78
+
Plan Rows: 1
+
Plan Width: 0
+
Actual Startup Time: 0.153
+
Actual Total Time: 0.153
+
Actual Rows: 0
+
Actual Loops: 1
+
Plans:
+
- Node Type: "Bitmap Index Scan"
+
Parent Relationship: "Member"
+
Parallel Aware: false
+
Index Name: "users_lower_idx"
+
Startup Cost: 0.00
+
Total Cost: 4.66
+
Plan Rows: 50
+
Plan Width: 0
+
Actual Startup Time: 0.048
+
Actual Total Time: 0.048
+
Actual Rows: 1
+
Actual Loops: 1
+
Index Cond: "(lower((city)::text) = 'melanyfort'::text)"
+
- Node Type: "Bitmap Index Scan"
+
Parent Relationship: "Member"
+
Parallel Aware: false
+
Index Name: "lower_state_users_idx"
+
Startup Cost: 0.00
+
Total Cost: 5.87
+
Plan Rows: 211
+
Plan Width: 0
+
Actual Startup Time: 0.102
+
Actual Total Time: 0.102
+
Actual Rows: 211
+
Actual Loops: 1
+
Index Cond: "(lower((state)::text) = 'ohio'::text)"
+
Planning Time: 0.260
+
Triggers:
+
Execution Time: 0.249
(1 row)
aruprakshit=#
------
In the first node type of "Bitmap Index Scan” on “users_lower_idx”, I see the
plan rows are 50, but actual rows it got 1. In the second node type of "Bitmap
Index Scan” on “ lower_state_users_idx”, I see the plan rows are 211, and
actual rows 211. Both are same. Based on what conditions planner estimated the
planned and actual rows count?
In node type “BitmapAnd”, I see again the actual rows 1, then why on the final
plan i.e. Node Type: "Bitmap Heap Scan” again planner estimated rows 1? How
does it counts these? What does the Loops count says us about the query?
Thanks,
Arup Rakshit
a...@zeit.io
