we are using postgres 13.7 My company runs a fairly complicated analytical query, unfortunately, I'm not allowed to disclose the query here. The explain plan has around 30 nodes and joins about 10 tables in various ways. The number of rows in the tables is not that many (ranging from about 100k to 1M rows)

So the query does not cause any disk iops at all (as shown by iostat -xkcd 2). However it constantly takes around 40% CPU on a 4 core system for hours together. I had to kill it after 8 hours.

I tried using perf to see a FlameGraph of CPU stacks but unfortunately I wasn't able to get good stack traces (unknown symbols) even though I installed all possible debug symbols.

I checked the pg_stat_activity table and I don't think its any locking issue (as also evidenced by matching the pid with the output of high CPU pid of top)

So my question is: Without the query going to completion, how can I check which stage of the plan is the bottleneck? And it is especially mysterious because the bottleneck is CPU and not disk

Edit: Following suggestion section, here is the obfuscated query plan https://explain.depesz.com/s/dGYI

  • You can provide an obfuscated plan by going here?
    – Vérace
    Jul 30, 2022 at 19:38
  • thanks, updated the question with the obfuscated plan
    – sha
    Jul 30, 2022 at 19:47
  • The plan generated using explain (analyze, buffers) would be more helpful
    – user1822
    Jul 30, 2022 at 20:11
  • unfortunately, the analyze runs the query and it never completes even after 8 hours :(
    – sha
    Jul 30, 2022 at 21:24
  • Either have the patience to leave it running over night with analyze. Or cut it down in a way that won’t change the plan - if you’re filtering on a date and it’s driving the query plan with that filter then choose a narrower date range. It might not be possible to get the exact plan again as estimates get reduced too so you’ll need to see what the differences are and use your knowledge of your data to figure out what that implies Aug 1, 2022 at 0:41


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.