Hi,
On 2025-10-08 00:13:34 +0200, Marco Boeringa wrote:
> This looks much better, doesn't it?
It indeed does!
> I hope this helps. Let me know if you need anything else.
> *** sudo perf -p <PID of one stuck postgres backend> -g -d 10 ***
> *** sudo perf report -g ***
Could you show perf report --no-children? That would show us which individual
functions, rather than call-stacks, take the longest.
> Samples: 40K of event 'task-clock:ppp', Event count (approx.): 10008250000
> Children Self Command Shared Object Symbol
> + 100,00% 0,00% postgres postgres [.] _start
> + 100,00% 0,00% postgres libc.so.6 [.]
> __libc_start_main@@GLIBC_2.34
> + 100,00% 0,00% postgres libc.so.6 [.]
> __libc_start_call_main
> + 100,00% 0,00% postgres postgres [.] main
> + 100,00% 0,00% postgres postgres [.] PostmasterMain
> + 100,00% 0,00% postgres postgres [.] ServerLoop.isra.0
> + 100,00% 0,00% postgres postgres [.]
> postmaster_child_launch
> + 100,00% 0,00% postgres postgres [.] 0x00005f3570fb9dbf
> + 100,00% 0,00% postgres postgres [.] PostgresMain
> + 100,00% 0,00% postgres postgres [.] exec_simple_query
> + 100,00% 0,63% postgres postgres [.] ExecNestLoop
> + 100,00% 0,00% postgres postgres [.] PortalRun
> + 100,00% 0,00% postgres postgres [.] PortalRunMulti
> + 100,00% 0,00% postgres postgres [.] ProcessQuery
> + 100,00% 0,00% postgres postgres [.] standard_ExecutorRun
> + 100,00% 0,00% postgres postgres [.] ExecModifyTable
> + 94,63% 1,47% postgres postgres [.] ExecScan
> + 78,76% 1,49% postgres postgres [.] IndexNext
> + 66,89% 1,96% postgres postgres [.] index_fetch_heap
> + 64,35% 3,61% postgres postgres [.]
> heapam_index_fetch_tuple.lto_priv.0
So somehow >60% of the CPU time is spent fetching tuples corresponding to
index entries. That seems ... a lot. Is it possible that you have a lot of
dead rows in the involved tables?
I don't immediately see how this could be related to AIO.
Greetings,
Andres Freund