Using "lightweight" functional indexes - BRIN

By Kaarel Moppel - Using BRIN to improve query performance - This is a quick performance tip for all those wrestling with occasional slow queries in PostgreSQL. There's a quite simple trick available that many people don't know about, that can be applied at "low cost" when slow queries are caused by poor planner estimates for your filter conditions. Read on for details.

The problem

So, the thing with Postgres statistics is that you basically lose them as soon as you transform the data column in some way in your query – the most prominent example for this would probably be something like "SELECT ... WHERE date_trunc('day', some_indexed_timestamp_column) = '2017-01-01"...and gone is the (imaginary) index and column statistics. That query could of course be easily rewritten so that the performance doesn't explode, but a functional index would also make sense here, whena  rewrite is not possible or the query is executed often and is selective enough. But for queries/filters that are used very rarely, a full functional index might not be ideal due to performance side-effects (index maintenance slows down updates, deletes, inserts a bit)...so welcome our performance helper of the day - BRIN functional indexes!

Example use case

So now let's paint it red with a dummy example: let's say we want to select all ID's that are dividable by 10 out of 1 million sequentially growing IDs. Let's create the test data and look at the resulting query plan.

What do we see? Well, we see that Postgres expects to get only 5000 rows, but actually we know that there would be 100k rows matched! Such a misestimation of 20x could already end really bad for larger datasets. And where does the number 5000 come from? Now some "insider knowledge" is required – one just needs to know that when Postgres doesn't have exact statistics for a column filter (as we actually transformed our ID column that we have statistics on), it by default figures that something between 0.33333 and 1% of data will be returned, depending on datatype and filter type (normal/join) - here 0.5%. So a constant...which obviously doesn't work too well for our less selective filter. But OK, how to improve the situation?

Using functional BRIN indexes for better row amount estimations

So now the trick - let's create our BRIN functional index and see how it changes our original query plan row estimate. BRIN by the way stands for Block Range (a.k.a. Min/Max) index - a lossy (table re-check needed) index type introduced in Postgres 9.5, generally meant for big amounts of data, most effective when the indexed columns are appearing in the table also somewhat naturally ordered. So let's create one, rebuild statistics and let's do another EXPLAIN.

And indeed, the estimation of 98467 matching rows looks now a lot better - quite exact actually!
The database of course still decided for a sequential scan, but at least it did it based on right assumptions (based on row count and table location correlation, see below for the corresponding pg_stats entry) and after a quick check, forcing Postgres to use the index, I saw that it was faster with a scan. And the efficiency gain compared to a normal functional index (as the index wouldn't be used anyways, just the index statistics) – 384x less space on the disc + faster data modifications as BRIN indexes need only maintenance when min/max threshold is crossed for a range of blocks!

Also note that for our case the row estimation worked so well as we have less than default_statistics_target (100 by default) unique values in our derived column so that Postgres has more or less exact appearance frequencies available for single values.

Hope this trick will serve you well 🙂

Get the most current information concerning indexes in PostgreSQL, visit our Index Blog Spot here.