🔗 Just because you’re getting an index scan, doesn’t mean you can’t do better!

An issue I often see folks missing when reviewing query plans, is that all of their scans involve indexes, so they think that the query is likely already as fast (or efficient) as it can be.

Many know that a Seq Scan with a high number of Rows Removed by Filter is a sign an index could help. But the reason an index speeds things up there applies just as much to other scans with large filters, where a better suited index could speed things up! While often we will see index filters show as “Rows Removed by Filter”, sadly there are cases that aren’t currently reported by EXPLAIN (but we’ll get to that later).

In this post we’ll look through several examples, some things to look out for, and how to then go about making things faster.

It’s worth noting that there are other ways that index use can still be optimised further. One such example that we also highlight via the “Index Efficiency” tips in pgMustard are a high number of Rows Removed by Index Recheck. But this post will be long enough already, so we’ll take a look at those another day.