Help to understand Actual Rows vs Plan Rows from the query planner output

From: Arup Rakshit <ar(at)zeit(dot)io>
To: pgsql-general(at)postgresql(dot)org
Subject: Help to understand Actual Rows vs Plan Rows from the query planner output
Date: 2018-09-23 20:21:19
Message-ID: C2837E8C-4392-4D7B-BFF8-CEF557776910@zeit.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Thanks,

Arup Rakshit
ar(at)zeit(dot)io

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2018-09-23 21:34:40 Re: heads up on large text fields.
Previous Message Adrian Klaver 2018-09-23 14:35:39 Re: PostgreSQl, PHP and IIS