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.
Post History
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 do...
#3: Post edited
by
Emily
·
2023-09-19T17:42:03Z (8 months ago)
I have a large and complex PostgreSQL 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.)
- 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.)
#2: Post edited
by
Emily
·
2023-09-18T20:01:48Z (8 months ago)
clarity
I have a large and complex PostgreSQL query that I would like to make faster. `EXPLAIN` suggests it should run quickly, with the worst parts being scans over 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.)
- I have a large and complex PostgreSQL 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: Initial revision
by
Emily
·
2023-09-18T20:01:07Z (8 months ago)
Alternatives to `EXPLAIN ANALYZE` for queries that won't complete
I have a large and complex PostgreSQL query that I would like to make faster. `EXPLAIN` suggests it should run quickly, with the worst parts being scans over 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.)