CODEWITHSUNDEEP
postgresqlpartial-index
Lego490
Lego490

Reputation: 185

Indexing on a mostly 0 column not working

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.

Upvotes: 0

Views: 84

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

Upvotes: 1

Related Questions