It’s hard to say anything new about query optimization. On the one hand, each new Postgres release includes multiple query planner improvements, and it feels like there is something for any problem that can possibly arise. On the other hand, the fundamental principles of optimization do not change: if your query is highly selective, meaning the result is a small percentage of the original data set, you need to build indexes that would support this particular search. If you are optimizing an analytical query, you are looking for the way to execute it in parallel and aggregate early.
There is only one “but” – it’s not like you can build an index on any table at any time. If that’s the case, what can you do?
Recently, I had to find a way to speed up a production query that suddenly started performing significantly slower than it used to. Yes, it reached the tipping point, but nevertheless, I had to find a way to make it fast again. Or at least not terribly slow.
Here is a problem I had to solve.
Given
- Postgres version: 13.6
- A monolithic (non-partitioned) table, size 750 GB, 16 billion rows
- Several indexes, but none of them were super useful for this particular search
And there is a query I needed to optimize. Yes, it looks simple/obvious, but wait till I get to the details!
Date could be any date; I used August 16 for illustration (and no, it’s not “yesterday” or “today”; the query could run for any date in the past). Basically, what you need is to find all records in which the interval from start_date to end_date includes that date in question (and satisfies other selection criteria). The query was running from several seconds to several minutes.
Yes, we know that we need: we need to build a daterange from start_date to end_date, and then build a GIST index on that range. All good, except we all know how long it takes to build any index on 16 billion rows, and if we are talking about GIST, it would easily take longer than 24 hours.
Also:
- there was no index that would have all of the search fields
- the highest selectivity column was
end_date, and there was no index which would have the end_date as a first column
- The most suitable index was on (
a, start_date, end_date), but a had the lowest selectivity.
The latter index was used, but the queries were still super slow, because if the day you search is for August 24, pretty much all records satisfy the condition on start_date, which resulted in too many reads and filtering too many records. As I said, building any indexes was not an option. What could I do?!
****
I started by suggesting adding an excessive selection criterion on the start date, figuring the actual start can’t be that far away in the past:
But I was wrong. I was told I need to capture “everything,” so I had to go to the very first recorded occurrence of the value in column a, but that query wasn’t super fast either, because this minimum date could be way too far in the past.
Then the customer asked whether it was possible to make their lives better for just a subset of queries, and I thought I could at least build a couple of partial indexes concurrently, so I asked them which conditions they wanted to run faster. They sent me an Excel sheet, and I built these partial indexes, with start_date first, then end_date, then column a; for this subset, the queries without any changes started running very fast (<100 ms), so the crisis was partially resolved. But then, I looked one more time at that Excel and realized that for each combination, they also sent me the “first day occurred”! I quickly loaded the Excel into a new table, making a, b, and c a unique combination, and modified the query like that:
I ran this query while the last partial index hadn’t been built yet, using the combination that would be covered by the new index, and it worked perfectly (also under 100 ms). Next, I asked the customer whether they could load the complete list, and how big it would be. They said it’s just a little bit over 2K, and they could definitely insert the remaining records. However, after giving it some thought, they got back to me saying that this list is a table in a different database, and they do not want to maintain the same data in two places, so they will just execute a call to this other database in their code and pass the first date to the query we were optimizing. I said that it was fine; the one round-trip penalty was minimal, and I understood they didn’t want redundancy.
However, on Monday (did I have to say it all happened on Friday?!) I had another thought. If the customer runs two queries in the app, they won’t be able to run this query outside the app and measure performance, so I asked whether they would be willing to try a foreign table. With all precautions of read-only permissions on just one table, the final query was:
WITH min_start_day AS MATERIALIZED
(SELECT start_date AS first_date
FROM foreign_table
WHERE a =? AND b=? AND c=?
)
SELECT * FROM t
WHERE a=? AND b=? AND c=?
AND start_date <='2026-08-16' AND end_date >='2026-08-16'
AND start_date >= (select first_date from min_start_day)
Again, executed in the same milliseconds.
The takeaways.
- When you can’t build a new index due to time/size constraints, sometimes you can build an interim “indexing table”: the table has the index you need, is smaller than the one you actually need to index, and it references an existing index
- The most powerful optimization tool was, is, and will always be looking at the actual data and how different parts are related in real life (or its representation:)).
- Knowing not what the query reads,but what goal we are trying to achieve is critical
… and now I know why these data centers need to much energy! I was a dead body after I was done!
The World of Data 