Post History

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](https://dba.stackexchange.com/questions/274735/unable-to-save-a-stored-procedure-which-contains-a-temporary-table-creation-and).

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](https://www.postgresql.org/docs/current/indexes-index-only-scans.html)