BRIN index usage-CodePudding

I can not convince PostgreSQL to use my BRIN index. I have tested on PostgreSQL 14.2 and 11.1. Here is my initial setup.

SET max_parallel_workers_per_gather = 0;
DROP TABLE IF EXISTS Measure;    
CREATE TABLE Measure (
    id int,
    sensor_id int,
    temperature int
);

INSERT INTO Measure(id, sensor_id, temperature)
VALUES (generate_series(1, 1000000),
        round(random()*100000)::int,
        round(random()*100)::int);

DROP INDEX IF EXISTS idxbrin_measure_sensor_id;    
CREATE INDEX idxbrin_measure_sensor_id ON Measure USING brin(sensor_id);

When I run a simple query returning around 10 rows such as

EXPLAIN SELECT * FROM Measure WHERE sensor_id = 10;

the BRIN index is not used:

Seq Scan on measure  (cost=0.00..17906.00 rows=10 width=12)
  Filter: (sensor_id = 10)

What I'm doing wrong?

CodePudding user response：

A BRIN index can only be used if the logical ordering of the indexed value correlates almost perfectly with the physical ordering in the table. So it would work for the id column (if you never delete or update any rows), but not for sensor_id. Note that for a small table like that, a BRIN index is not very useful.

CodePudding user response：

As pointed out by Laurenz Albe, the BRIN index key has to correspond to the physical ordering of the data. Therefore, I ordered the table according to the sensor_id and created the BRIN index, and PG used it.

CREATE INDEX idx_measure_sensor_id ON MEASURE(sensor_id);
CLUSTER MEASURE USING idx_measure_sensor_id;
DROP INDEX IF EXISTS idx_measure_sensor_id;
CREATE INDEX idxbrin_measure_sensor_id ON Measure USING brin(sensor_id);

EXPLAIN SELECT * FROM Measure WHERE sensor_id = 10;

In such a case the result plan is

Bitmap Heap Scan on measure  (cost=20.39..148083.72 rows=2 width=1016)
  Recheck Cond: (sensor_id = 10)
  ->  Bitmap Index Scan on idxbrin_measure_sensor_id  (cost=0.00..20.38 rows=416427 width=0)
        Index Cond: (sensor_id = 10)