Making auto_explain more useful / convenient

From: Vladimir Churyukin <vladimir(at)churyukin(dot)com>
To: pghackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Making auto_explain more useful / convenient
Date: 2023-11-11 10:17:17
Message-ID: CAFSGpE11ckWOfm8-VR3VHfuhtvU6KqjfNpqnYSSAi+NYNA03kg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I'm not sure if these ideas were circulated before or not.
We use auto_explain a lot to investigate slow/problematic queries.
One of the main issues with its usability comes from the fact that EXPLAIN
output is logged rather than returned to the caller in some way. If you
have a large cluster with lots of replicas, there is also an extra
inconvenience of log accumulation, search, etc.
Why not have an option to return EXPLAIN results as a NoticeResponse
instead? That would make its usage more convenient.

Another thing is tangentially related...
I think it may be good to have a number of options to generate
significantly shorter output similar to EXPLAIN. EXPLAIN is great, but
sometimes people need more concise and specific information, for example
total number of buffers and reads by certain query (this is pretty common),
whether or not we had certain nodes in the plan (seq scan, scan of certain
index(es)), how bad was cardinality misprediction on certain nodes, etc.
It's not totally clear yet what would be the best way to define those
rules, but I think we can come up with something reasonable. Logging or
returning shorter messages like that can cause less overhead than logging
full EXPLAIN and can potentially allow for better query monitoring overall.

Do you see any potential issues with implementing those? Of course there
should be more details, like what kind of configuration parameters to add,
how to define rules for the 2nd case, etc. Just wanted to check if there
are any objections in general.

Thank you,
-Vladimir Churyukin.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Koval 2023-11-11 10:26:03 Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Previous Message John Naylor 2023-11-11 09:33:11 Re: [PATCH] New [relation] option engine