Date: Mon, 13 May 2024 18:16:13 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Siraj G <tosiraj.g@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Not clear with the Explain Plan output
Message-ID: <ZkI8zYzpoAAG0qGi@depesz.com>
Reply-To: depesz@depesz.com
References: 
 <CAC5iy60e7T3LUcVN94pOYxgnxvQPqrMUv-87qCUNnNoC_+gvLw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAC5iy60e7T3LUcVN94pOYxgnxvQPqrMUv-87qCUNnNoC_+gvLw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZkI8zYzpoAAG0qGi%40depesz.com>
Precedence: bulk

On Mon, May 13, 2024 at 09:07:14PM +0530, Siraj G wrote:
> Hello experts!
> 
> I am new in the PgSQL domain, so I need assistance in understanding the
> explain plan. I took the plan of a SQL via PgAdmin and it looks like the
> attached one. I am unable to see the numbers that indicate where the heavy
> processing is being done, to determine the IO and cpu processing.
> 
> Is this the right way to collect explain plan? If so, how do I interpret
> this? If there is another or a better way to collect this, please let me
> know.

Just run 'explain analyze' and read the text output. Graphs, in my
opinion, hide too much info.

Normal explain looks like:

#v+
                                                                  QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Sort  (cost=35.41..35.50 rows=35 width=224) (actual time=0.160..0.162 rows=4 loops=1)
   Sort Key: n.nspname, c.relname
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=15
   ->  Hash Join  (cost=1.09..34.51 rows=35 width=224) (actual time=0.046..0.153 rows=4 loops=1)
         Hash Cond: (c.relnamespace = n.oid)
         Buffers: shared hit=15
         ->  Seq Scan on pg_class c  (cost=0.00..32.16 rows=70 width=77) (actual time=0.009..0.107 rows=146 loops=1)
               Filter: ((relkind = ANY ('{r,p,v,m,S,f,""}'::"char"[])) AND pg_table_is_visible(oid))
               Rows Removed by Filter: 280
               Buffers: shared hit=14
         ->  Hash  (cost=1.07..1.07 rows=2 width=68) (actual time=0.029..0.029 rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               Buffers: shared hit=1
               ->  Seq Scan on pg_namespace n  (cost=0.00..1.07 rows=2 width=68) (actual time=0.013..0.015 rows=2 loops=1)
                     Filter: ((nspname <> 'pg_catalog'::name) AND (nspname !~ '^pg_toast'::text) AND (nspname <> 'information_schema'::name))
                     Rows Removed by Filter: 3
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=9
 Planning Time: 0.204 ms
 Execution Time: 0.189 ms
(22 rows)
#v-

Best regards,

depesz