Horton

Re: RFC: Logging plan of the running query

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: torikoshia <torikoshia@oss.nttdata.com>, pgsql-hackers@lists.postgresql.org, Étienne BERSAC <etienne.bersac@dalibo.com>, Andres Freund <andres@anarazel.de>, jtc331@gmail.com, rafaelthca@gmail.com
Date: 2024-02-12T00:00:00Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. ecpg: Catch zero-length Unicode identifiers correctly

  2. Improve warning message in pg_signal_backend()

  3. Add assert to ensure that page locks don't participate in deadlock cycle.

On Wed, Feb 7, 2024 at 12:58 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> >
> > >  */
> > > How bad this performance could be. Let's assume that a query is taking
> > > time and pg_log_query_plan() is invoked to examine the plan of this
> > > query. Is it possible that the looping over all the locks itself takes
> > > a lot of time delaying the query execution further?
> >
corner case test:
pgbench  --initialize --partition-method=range --partitions=20000
Somehow my setup, the pg_bench didn't populate the data but there are
20000 partitions there.
(all my other settings are default)

some interesting things happened when a query touch so many partitions like:
select abalance, aid from public.pgbench_accounts,pg_sleep(4) where aid > 1;

in another session, if you immediate call SELECT pg_log_query_plan(9482);
then output be
`
LOG:  backend with PID 9482 is not running a query or a subtransaction
is aborted
`
however if you delay a little bit of time (like 1 second), then
LOG will emit the plan with lots of text (not sure the plan is right).

I think the reason is that the `InitPlan` within
standard_ExecutorStart takes more time to finish
when your query touches a lot of partitions.