Indexing on a mostly 0 column not working

Question

I've been having troubles with trying to use an index on my table.

For table items, I have a column named market. If market = 0, then it's not for sale - if it's a value greater than 0 (for example, 100), it is for sale, for $100.

However, I have tens of millions of rows of items, that aren't for sale. There's probably around 1000 or so items at sale (with a price) at a time.

I'm trying to have a fast query for selecting all rows in which market is greater than 0;

SELECT market FROM items WHERE market > 0.

However, this takes relatively long and uses a SEQ scan instead of the index I have on market. Any help would be appreciated, or another approach to my question.

Answer 1

You can write partial index on your table items

CREATE INDEX idx_items_inactive
ON customer(market)
WHERE market> 0;

It will use Index scan in case of query mentioned in the question