Postgres wont use the proper index

I have the following table:

id       : id
dim_id   : fk
day      : date
value    : int

With the following indexes:

id             : BTREE
dim_id         : BTREE
dim_id, day    : BTREE
day, value     : BRIN

When I make the query

EXPLAIN ANALYSE
SELECT *
FROM table_name
WHERE day = '2019-11-18'
ORDER BY day, value

The query takes way too long because the index it uses is the dim_id, day and then sorts the result, which completely defeats the purpose of having the day,value index. The reason this last index is BRIN and not BTREE, is that otherwise the indexes alone were using 200GB, now is more manageable.

Any ideas what may be wrong?

Answer

The index on dim_id alone seems useless. You can save space and data modification time by dropping it.

A BRIN index cannot be used to support ORDER BY, and it won’t work at all unless rows are inserted in index order and there are no updates or deletes on the table.

If that is the case, you can use a BRIN index on day. The alternative is a B-tree index on (day,value).

Advertisement

Answer