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 <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. Thanks, Arup Rakshit a...@zeit.io > On 25-Sep-2018, at 11:45 AM, Laurenz Albe <laurenz.a...@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 >
