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
