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
Parent
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.)
Post
Note: I have limited experience with PostgreSQL, but extensive experience working with SQL Server, so not everything below might apply to PostgreSQL.
I have a large and complex PostgreSQL query that I would like to make faster. (..) When run, it does not complete in any reasonable amount of time
I would assume we are talking about a SELECT statement. One quick change to try would be using a LIMIT and see if the query ends for a small amount of returned rows.
However, I think that the real issue is that the query became large and complex. This should be broken into multiple statements with the help of temporary tables. This can also be encapsulated in a stored procedure.
The code structure can look like the following:
- create the temporary table (i.e. empty, contains the output structure)
- minimally populate the temporary table, for example having only a few columns populated with values (the rest remain NULL)
- add UPDATE statements to deal with the rest of the columns. Define as many UPDATEs as are needed to have a good enough performance
Another advantage of this approach is readability (smaller queries) and maintainability (e.g. easier to change when a column is added as this affects a small query).
Other things to consider:
- historical data - if the query deals with historical data aggregates, these can be precomputed in some persisted tables
- indexes - consider adding covering indexes
1 comment thread
1 comment thread