Обсуждение: Help to understand Actual Rows vs Plan Rows from the query planneroutput
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?
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 regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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?
On 24-Sep-2018, at 4:11 AM, Tomas Vondra <tomas.vondra@2ndquadrant.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
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Arup Rakshit wrote: > 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. That is true; a truth that many developers unfortunately ignore. > 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. This is a broad topic, and I can only give you some hints. In order to understand EXPLAIN output and to improve your query, you need to know some of how the database is implemented. You have to understand index scans, index only scans and bitmap index scans. You have to understand nested loop, hash and merge joins. You have to understand table statistics, dead tuples and table bloat. The first things to look for in EXPLAIN (ANALYZE, BUFFERS) output is in which nodes the time is spent, and where the estimated number of rows diverges significantly from the actual number of rows. The former are the spots where there is room for improvement, and the latter is often the root cause of a bad plan choice. Also, watch out for the nodes that touch a lot of blocks. They can cause intermittent slow-down if the blocks are not in cache. > Also what are the best resources to learn GIST, GIN indexes — something which > teaches it from the ground up? The documentation, and for what goes beyond that, the source. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hi,
> This is a broad topic, and I can only give you some hints.
Yes when I am reading this https://www.postgresql.org/docs/10/static/using-explain.html I found the doc said like “...Plan-reading is an art that requires some experience to master, but this section attempts to cover the basics..”
Lets see how can I get there. :) I probably need to ask a lot of questions.
Thanks for sharing those points. I made a note about them.
On 25-Sep-2018, at 11:45 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:Arup Rakshit wrote: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.
That is true; a truth that many developers unfortunately ignore.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.
This is a broad topic, and I can only give you some hints.
In order to understand EXPLAIN output and to improve your query, you need
to know some of how the database is implemented.
You have to understand index scans, index only scans and bitmap index scans.
You have to understand nested loop, hash and merge joins.
You have to understand table statistics, dead tuples and table bloat.
The first things to look for in EXPLAIN (ANALYZE, BUFFERS) output is in which
nodes the time is spent, and where the estimated number of rows diverges
significantly from the actual number of rows.
The former are the spots where there is room for improvement, and the latter
is often the root cause of a bad plan choice.
Also, watch out for the nodes that touch a lot of blocks.
They can cause intermittent slow-down if the blocks are not in cache.Also what are the best resources to learn GIST, GIN indexes — something which
teaches it from the ground up?
The documentation, and for what goes beyond that, the source.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com