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? |
