Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Comments on Alternatives to `EXPLAIN ANALYZE` for queries that won't complete
Post
Alternatives to `EXPLAIN ANALYZE` for queries that won't complete
I have a large and complex PostgreSQL SELECT query that I would like to make faster. EXPLAIN suggests it should run quickly, with the worst parts being scans of a few thousand rows. When run, it does not complete in any reasonable amount of time (if statement_timeout is set to infinite, it eventually still gives up, complaining about having exceeded temporary file size limits, suggesting something is loading way more data than expected).
Usually, this would suggest to me that EXPLAIN's estimates are horribly inaccurate in some way, and I would try EXPLAIN ANALYZE to see what's really happening. But since this particular query is so bad I can't run it at all, I also can't run it with EXPLAIN ANALYZE.
What other tools are at my disposal for this sort of situation? Can I ask PostgreSQL for some sort of partial or time-limited EXPLAIN ANALYZE, as in "run this for five minutes, then stop and tell me what you spent those five minutes doing"? If I start commenting out bits of the query until it goes fast again, can I rely on the results being accurate, or does PostgreSQL's optimizer work more globally than that?
(Query itself omitted because I've run into this situation a few times, and would like general strategies rather than an answer for this specific query.)
1 comment thread