Re: RFC: Logging plan of the running query

torikoshia <torikoshia@oss.nttdata.com>

From: torikoshia <torikoshia@oss.nttdata.com>

To: Robert Haas <robertmhaas@gmail.com>

Cc: Julien Rouhaud <rjuju123@gmail.com>, James Coleman <jtc331@gmail.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers@lists.postgresql.org, Étienne BERSAC <etienne.bersac@dalibo.com>, ashutosh.bapat.oss@gmail.com, rafaelthca@gmail.com, jian.universality@gmail.com

Date: 2024-02-26T12:01:45Z

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 →

ecpg: Catch zero-length Unicode identifiers correctly
- a18b6d2dc288 15.0 cited
Improve warning message in pg_signal_backend()
- 7fa945b857cc 15.0 cited
Add assert to ensure that page locks don't participate in deadlock cycle.
- 72e78d831ab5 13.0 cited

Attachments

v37-0001-Add-function-to-log-the-plan-of-the-query.patch (text/x-diff) patch v37-0001

On 2024-02-24 00:23, Robert Haas wrote:
> On Fri, Feb 23, 2024 at 7:50 PM Julien Rouhaud <rjuju123@gmail.com> 
> wrote:
>> On Fri, Feb 23, 2024 at 10:22:32AM +0530, Robert Haas wrote:
>> > On Thu, Feb 22, 2024 at 6:25 AM James Coleman <jtc331@gmail.com> wrote:
>> > > This is potentially a bit of a wild idea, but I wonder if having some
>> > > kind of argument to CHECK_FOR_INTERRUPTS() signifying we're in
>> > > "normal" as opposed to "critical" (using that word differently than
>> > > the existing critical sections) would be worth it.
>> >
>> > It's worth considering, but the definition of "normal" vs. "critical"
>> > might be hard to pin down. Or, we might end up with a definition that
>> > is specific to this particular case and not generalizable to others.
>> 
>> But it doesn't have to be all or nothing right?  I mean each call 
>> could say
>> what the situation is like in their context, like
>> CHECK_FOR_INTERRUPTS(GUARANTEE_NO_HEAVYWEIGHT_LOCK | 
>> GUARANTEE_WHATEVER), and
>> slowly tag calls as needed, similarly to how we add already CFI based 
>> on users
>> report.
> 
> Absolutely. My gut feeling is that it's going to be simpler to pick a
> small number of places that are safe and sufficient for this
> particular feature and add an extra call there

Hmm, whether extending CHECK_FOR_INTERRUPTS() or adding extras call 
directly, currently I'm not sure where are the good 'places', which 
don't give performance impact.

As attached PoC patch, I experimentally added extra calls on 
ExecScanFetch() which would be less called than ExecProcNode()[1].
When running sequential scan on pgbench_accounts which is on the memory, 
there seems a performance degradation.

   - Executed "select * from pgbench_accounts" for 20 times
   - Compared the elapsed time between the patch applied and not applied 
on 874d817baa160ca7e68
   - there were no heap_blks_read during the query
   - pgbench_accounts has 3000000 rows

   patch NOT applied:
   - average: 335.88 ms
   - max: 367.313 ms
   - min: 309.609 ms

   patch applied:
   - average: 342.57 ms
   - max: 380.099 ms
   - min: 324.270 ms

It would be nice if there was a place accessed once every few seconds or 
so..

[1] 
https://www.postgresql.org/message-id/20240215185911.v4o6fo444md6a3w7%40awork3.anarazel.de

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation