Re: RFC: Logging plan of the running query - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: RFC: Logging plan of the running query
Date
Msg-id CAFj8pRDQDHP-hzMF-Hph99aAJpkCJ3y+F-eKmxiMD7sCrPUFFQ@mail.gmail.com
Whole thread Raw
In response to RFC: Logging plan of the running query  (torikoshia <torikoshia@oss.nttdata.com>)
List pgsql-hackers


st 12. 5. 2021 v 13:24 odesílatel torikoshia <torikoshia@oss.nttdata.com> napsal:
Hi,

During the discussion about memory contexts dumping[1], there
was a comment that exposing not only memory contexts but also
query plans and untruncated query string would be useful.

I also feel that it would be nice when thinking about situations
such as troubleshooting a long-running query on production
environments where we cannot use debuggers.

At that point of the above comment, I was considering exposing
such information on the shared memory.
However, since memory contexts are now exposed on the log by
pg_log_backend_memory_contexts(PID), I'm thinking about
defining a function that logs the plan of a running query and
untruncated query string on the specified PID in the same way
as below.

   postgres=# SELECT * FROM pg_log_current_plan(2155192);
    pg_log_current_plan
   ---------------------
    t
   (1 row)

   $ tail -f data/log/postgresql-2021-05-12.log

   2021-05-12 17:37:19.481 JST [2155192] LOG:  logging the plan of
running query on PID 2155192
           Query Text: SELECT a.filler FROM pgbench_accounts a JOIN
pgbench_accounts b ON a.aid = b.aid;
           Merge Join  (cost=0.85..83357.85 rows=1000000 width=85)
             Merge Cond: (a.aid = b.aid)
             ->  Index Scan using pgbench_accounts_pkey on
pgbench_accounts a  (cost=0.42..42377.43 rows=1000000 width=89)
             ->  Index Only Scan using pgbench_accounts_pkey on
pgbench_accounts b  (cost=0.42..25980.42 rows=1000000 width=4)


Attached a PoC patch.

Any thoughts?

+1

Pavel


[1]
https://www.postgresql.org/message-id/CA%2BTgmobkpFV0UB67kzXuD36--OFHwz1bs%3DL_6PZbD4nxKqUQMw%40mail.gmail.com


Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

pgsql-hackers by date:

Previous
From: Pavel Borisov
Date:
Subject: Re: OOM in spgist insert
Next
From: Amit Langote
Date:
Subject: Re: Query regarding RANGE Partitioning