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

From Julien Rouhaud
Subject Re: RFC: Logging plan of the running query
Date
Msg-id 20210512144046.tjyg7wxcndd2dmmu@nol
Whole thread Raw
In response to RFC: Logging plan of the running query  (torikoshia <torikoshia@oss.nttdata.com>)
List pgsql-hackers
On Wed, May 12, 2021 at 08:24:04PM +0900, torikoshia wrote:
> 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)

I didn't read the POC patch yet, but +1 for having that feature.



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Do we need to rethink how to parallelize regression tests to speedup CLOBBER_CACHE_ALWAYS?
Next
From: Dilip Kumar
Date:
Subject: Re: OOM in spgist insert