Re: Help to understand Actual Rows vs Plan Rows from the query planner output

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
ar(at)zeit(dot)io

> On 25-Sep-2018, at 11:45 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>