Re: RFC: Logging plan of the running query

On Tue, Jun 22, 2021 at 8:00 AM torikoshia  
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 FORMAT 
> TEXT
> and VEBOSE are used in the 
> EXPLAIN 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