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 a...@zeit.io > On 24-Sep-2018, at 4:11 AM, Tomas Vondra <tomas.von...@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 > <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
