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 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 computesestimates in general, or how it computed these particular estimates (orwhat issues you see there).Perhaps this would give you at least some answers:https://www.postgresql.org/docs/11/static/row-estimation-examples.htmlregards-- Tomas Vondra http://www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
|
