Index on Column with Very Few Distinct Values

Question

I have to query a table of stock prices that includes a column 'split_coefficient' where split_coeffieint is almost always 1.0, but very occasionally is something other than that: 2.0, 0.5, etc.

I have tried adding a partial index on split_coefficient where 'split_coefficient != 1.0', but postgres does not use it in its query plan. Also tried, just a normal index on split_coeffient, which was not used either.

I typically query 'select * from fmvs where commodity_id == <SOME_ID> AND date BETWEEN and AND split_coefficient <> 1.0'.

I have an index on commodity_id, and date. What is the best way to index on split_coeficient to speed up the query.

Answer 1

You stated in your question

I typically query 'select * from fmvs where commodity_id == <SOME_ID> AND date BETWEEN and AND split_coefficient <> 1.0'.

and

I have an index on commodity_id, and date

You need a compound index with all threee columns in this order

CREATE INDEX covering_index ON fmvs USING btree(commodity_id,date,split_coefficient);

That way your query explain plan should perform an index scan on the three columns. The scan will be narrowed down to a date range scan on commodity_id and look got those split_coefficient values before reaching out to the table for row data.

Having an individual index on commodity_id and another individual index on date will not suffice.