> Now the most common solution to less row lookups would be using an index. We could create one on created_at and call it a day. But we’d probably only see a ve

> Now the most common solution to less row lookups would be using an index. We could create one on created_at and call it a day. But we’d probably only see a very small speedup. Why is that? As we’re not interested in “the last 14 days of price changes overall” here, but only in “the last 14 days of price changes of one single reference”, what the database would need to do with two separate indices is called a bitmap scan. It would first look up everything that ever happened to the offers of this reference from the index, then fetch everything that happened within the last 14 days, then doing a bitmap scan (which is a fancy word for calculating the intersection between the slices) and only then look up the actual rows that lighted up in both result sets. If this sounds complex and taxing, it actually is - and for this reason it’s an optimization that often times so small the database won’t even attempt it. This isn't exactly accurate. A bitmap scan builds a compact representation (a bitmap) of the locations of all of the rows it needa to fetch. Postgres will build a bitmap for each index, intersect them to obtain a bitmap of all of the rows that pass both filters, then fetch those rows from the table. This is cheap since you only read the rows from the database that satisfy all of filters. You don't need to read all of the rows that pass either filter. Additionally, if effective_io_concurrency is tuned properly, a bitmap scan can potentially be much faster since Postgres will prefetch the rows it will need (Postgres doesn't do prefetching for a normal index scan). I once saw a query speed up 10x after tuning effective_io_concurrency and forcing a bitmap scan.