Help to understand Actual Rows vs Plan Rows from the query planneroutput - Mailing list pgsql-general
| From | Arup Rakshit |
|---|---|
| Subject | Help to understand Actual Rows vs Plan Rows from the query planneroutput |
| Date | |
| Msg-id | C2837E8C-4392-4D7B-BFF8-CEF557776910@zeit.io Whole thread Raw |
| Responses |
Re: Help to understand Actual Rows vs Plan Rows from the queryplanner output
|
| List | pgsql-general |
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?
pgsql-general by date: