1

I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory usage and long query times. When running locally on my machine, i'm seeing 6.5gb of memory used during the query, and the query takes around 8 seconds to fully run. However the weird part is that almost all of the time is spent in query planning, and not much in the actual execution:

Planning time: 7453.148 ms  
Execution time: 159.609 ms  

Interestingly enough, PREPARE-ing the query and then EXECUTE-ing it later causes the memory usage to spike during planning, but not during execution.

I have never seen a query take that long to plan, and none of our other large queries get anywhere near that.

My question is how can I diagnose or debug what the query planner is doing here? What tools does PostgreSQL provide to allow me to profile and diagnose why this query in particular is taking so much time and memory in the query planning stage, and what we can do to reduce the memory usage from the query planner.

Things i've tried:

  • Disabling GEQO entirely - had no measurable effect on the memory used, but did marginally increase the planning time to ~9 seconds up from ~7.5 seconds.
  • Tweaking GEQO threshold - had no measurable effect whether set to 2 or 99999 or anything in between.
  • Tweaking from_collapse_limit and join_collapse_limit - these had a small impact on the query, taking the planning down from ~7 seconds to ~5 seconds but not changing the memory usage at all (and the memory usage is really my main concern here)

I want to dig into this more, but without knowing how to profile what the query planner is doing, I'm basically shooting in the dark...

Edit: this is in PostgreSQL 9.6. We should be able to upgrade to 10 fairly easily, but 11 and up will need more research into if it's feasible.

  • 1
    How many tables are used in the query? Any partitioned tables? If yes, how many partitions? – Laurenz Albe Oct 30 '19 at 15:44
  • In addition to @LaurenzAlbe 's questions, what version of Postgres are you using? – jmelesky Oct 30 '19 at 16:00
  • @LaurenzAlbe There are no partitioned tables, and I'm not really able to easily find out how many distinct tables are in it, but the query does have about 600 joins in it (sadly that's not a typo). Most of them are LEFT JOIN LATERAL, and a significant number (at least 30, probably more) are joining the same table multiple times with different where clauses which is a quirk of how our system generates the queries. And @jmelesky it's postgres 9.5 in production, 9.6 locally. We are planning on testing with newer versions soon. – Klathmon Oct 30 '19 at 16:02
  • Well, then 7 seconds is pretty cool. If that monster actually is run more often, use a prepared statement or a PL/pgSQL function and cache the plans. – Laurenz Albe Oct 30 '19 at 16:31
  • Unfortunately preparing the statement isn't going to be that easy since the query is generated from a user-configurable system. It's an option that we are keeping open, but I'm really looking for how to profile what parts of the query are causing the long planning times so we can apply our dev resources to improve our query builder in areas that can have the biggest impact. – Klathmon Oct 30 '19 at 16:35
-1

The first stop to investigating this be would the system tool "perf". You can get the pid of the running backend (by using "top", for example--make sure you get the pid of the backend, not the client program) then do sudo perf top -p <pid>. You might get more useful output by first installing the debug symbols. How you do that is going to depend on your OS and how you installed it.

But once you identify the bottleneck, I wouldn't hold out much hope that anyone will do anything about it, given the far-out nature of your query. If you are willing to dig into the source code to propose our own solution, then I would suggest you start by compiling your own server with version 13dev. You aren't going to get very far in your investigations using only packaged versions, and especially not older packaged versions. (Also, the problem might already be fixed in 12 or 13dev).

  • That's what I was afraid of, I haven't touched the perf tool in years, but I guess I'm gonna dust it back off! And i'm not looking to try and get this kind of thing fixed upstream, I fully understand that such a monsterous query is way outside the norm and i'm impressed that postgres has been able to handle it as well as it has. I'm more looking to see what changes I can make to our query builder which will have the best bang for my buck, but without knowing what parts of the query are causing the most slowdown and resource usage, i'm not able to make good decisions there. – Klathmon Oct 30 '19 at 20:04
  • One tip would be to make sure every column and table is given an explicit and unique alias in your query text. If the same tables and columns appear over and over again, it can be N^2 or worse for postgresql to disentangle them and assign them internal aliases. – jjanes Oct 30 '19 at 20:09
  • I think that might be part of my problem! Because it's a generated query the system aliases everything in a way that causes a lot of "shadowed" aliases. Besides being a giant pain to read the generated queries, I hadn't thought it could cause any performance issues. I'll start there and see if I can make all the aliases unique. Thanks for the tip! – Klathmon Oct 30 '19 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.