Re: RFC: Logging plan of the running query

On Tue, Jun 22, 2021 at 8:00 AM torikoshia <torikoshia(at)oss(dot)nttdata(dot)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