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

Hello Thomas,

Thanks for the link. I read the documentation you linked, and part of it I understood and rest went above my head. Probably I need to read it multiple times to understand what is going on. I am learning how indexing works in DBMS. Mostly I understood Btree so far. I am an application developer. Being an application developer I think I need to know which column should be indexed and what kind of index to apply in which case. Most of the time, when I see slow query, I saw people ask to run the explain to see the plan. And explain statement shows lot of data about the query. So my questions is that: Which part I should read from the plan output to figure out reason of slowness or what need to be used to improve it. What are basic things I should know about it. I think, I don’t need to internal math for this, am I right? The query in this post is not a slow query, it is something I ran to see how index merge happens. I am asking generally. Can you give me some directions on this, so that I can build up some bases on this subject.

Also what are the best resources to learn GIST, GIN indexes — something which teaches it from the ground up?

Thanks,

Arup Rakshit
ar(at)zeit(dot)io

> On 24-Sep-2018, at 4:11 AM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
>
>
> On 09/23/2018 10:21 PM, Arup Rakshit wrote:
>> 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?
>>
>
> It's not very clear to me whether you're asking how the planner computes
> estimates in general, or how it computed these particular estimates (or
> what issues you see there).
>
> Perhaps this would give you at least some answers:
>
> https://www.postgresql.org/docs/11/static/row-estimation-examples.html <https://www.postgresql.org/docs/11/static/row-estimation-examples.html>
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services