MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Tue, 13 Jul 2021 23:11:37 +0900
From: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
To: torikoshia <torikoshia@oss.nttdata.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Fujii Masao
 <masao.fujii@oss.nttdata.com>, Dilip Kumar <dilipbalaut@gmail.com>, Pgsql
 Hackers <pgsql-hackers@postgresql.org>
Subject: Re: RFC: Logging plan of the running query
In-Reply-To: <d136f7cc678ba271f90e33a112b3669c@oss.nttdata.com>
References: <cf8501bcd95ba4d727cbba886ba9eea8@oss.nttdata.com>
 <CAFiTN-vE4Q6=bJ-n9hNz8qwkaw2NK1scNC5LrSFkREex+XymLg@mail.gmail.com>
 <CALj2ACXDiOhAdXks_w2b5NGg=7_K27Lx1Gau_iQnxGBCNkSMnQ@mail.gmail.com>
 <CALj2ACXpvVeLstnNG1Oris0VpxbSdLNdh_r-v6YxTEY5vcDHAA@mail.gmail.com>
 <CAFiTN-tbi83fB0j0S7-kj9TraUkim03M3S+oXdYe6sMZ7nxVYA@mail.gmail.com>
 <CALj2ACVH-fhO5htnM2UbV7mvP3+0+zYHBamCTC8KWTtSb5+8=g@mail.gmail.com>
 <CAFiTN-vQcDtyc5aRF1RNo-VgRzMcZ4E1qKTybvHpzUV1FEjqBA@mail.gmail.com>
 <CALj2ACWiVHpOqEztxHRtUR1oiDzPGUnguqv2EfEmQSv7jH93aw@mail.gmail.com>
 <CAFiTN-vOTFRK1xoAWRAGJ+54mSpDxmenAFexbySRhgy7hNH5Fg@mail.gmail.com>
 <CAFiTN-u6yqaxQyfX0jUMqDZ22fePfuQuQmzuozTxC34ca+ZByg@mail.gmail.com>
 <c6682a25f3f0e9bd520707342219eac5@oss.nttdata.com>
 <0842b5da697a03869fb50be93817e4e1@oss.nttdata.com>
 <CALj2ACUpznbsLbOg9mauSQZfU2eDxnNLYXcBHaAp1yWxMKLK9g@mail.gmail.com>
 <1c1590d3962cea6e06621da76677b5f3@oss.nttdata.com>
 <CALj2ACVeOvBcN2-MZh-P3FKxsxOv-DD=fUgAMQEY8vPEhoJUQw@mail.gmail.com>
 <dfc25182d91ed9491f77c0c8ca5b71fd@oss.nttdata.com>
 <64f716c44629e303b66e6c24502147cc@oss.nttdata.com>
 <CALj2ACXS8oSHbO_gf==jRLGGDoMR-AoeKhoSfd1uh8kw-keY7g@mail.gmail.com>
 <d136f7cc678ba271f90e33a112b3669c@oss.nttdata.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <eb5ede19715cc6ee2dfc8b5f25319043@oss.nttdata.com>
Archived-At: 
 <https://www.postgresql.org/message-id/eb5ede19715cc6ee2dfc8b5f25319043%40oss.nttdata.com>
Precedence: bulk

On Tue, Jun 22, 2021 at 8:00 AM torikoshia <torikoshia@oss.nttdata.com> 
wrote:
> Updated the patch.

Hi, torikoshi-san

Thanks for your great work! I'd like to use this feature in v15.
I confirmed that it works with queries I tried and make check-world has 
no error.

When I tried this feature, I realized two things. So, I share them.

(1) About output contents

> The format of the query plan is the same as when <literal>FORMAT 
> TEXT</literal>
> and <literal>VEBOSE</literal> are used in the 
> <command>EXPLAIN</command> command.
> For example:

I think the above needs to add COSTS and SETTINGS options too, and it's 
better to use an
example which the SETTINGS option works like the following.

```
2021-07-13 21:59:56 JST 69757 [client backend] LOG:  plan of the query 
running on backend with PID 69757 is:
         Query Text: PREPARE query2 AS SELECT COUNT(*) FROM 
pgbench_accounts t1, pgbench_accounts t2;
         Aggregate  (cost=3750027242.84..3750027242.86 rows=1 width=8)
           Output: count(*)
           ->  Nested Loop  (cost=0.84..3125027242.84 rows=250000000000 
width=0)
                 ->  Index Only Scan using pgbench_accounts_pkey on 
public.pgbench_accounts t1  (cost=0.42..12996.42 rows=500000 width=0)
                       Output: t1.aid
                 ->  Materialize  (cost=0.42..15496.42 rows=500000 
width=0)
                       ->  Index Only Scan using pgbench_accounts_pkey on 
public.pgbench_accounts t2  (cost=0.42..12996.42 rows=500000 width=0)
         Settings: effective_cache_size = '8GB', work_mem = '16MB'
```

(2) About EXPLAIN "BUFFER" option

When I checked EXPLAIN option, I found there is another option "BUFFER" 
which can be
used without the "ANALYZE" option.

I'm not sure it's useful because your target use-case is analyzing a 
long-running query,
not its planning phase. If so, the planning buffer usage is not so much 
useful. But, since
the overhead to output buffer usages is not high and it's used for 
debugging use cases,
I wonder it's not a bad idea to output buffer usages too. Thought?

Regards,
-- 
Masahiro Ikeda
NTT DATA CORPORATION