0

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.

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
Lego490
  • 135
  • 1
  • 12
  • Please post the table definition with indexes for your table `items` – Akhilesh Mishra Sep 08 '20 at 11:31
  • Works for me. Did you VACUUM ANALYZE, and what version of PostgreSQL are you using? If you want help interpreting the EXPLAIN ANALYZE, you will need to show it to us. – jjanes Sep 08 '20 at 21:13
  • Sure, I'm using ver 11 and here's what I get: `[, , , , ]` - sorry about the format, I'm printing it with Python – Lego490 Sep 08 '20 at 23:51

1 Answers1

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

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
  • Unfortunately I tried this and it still uses SEQ scan... however, I haven't put this into production yet. This means that all the items are currently unlisted (market = 0), except a few that I'm testing with (so this means, about 5 items that have market > 0 in 8 million rows.) Could this possibly be affecting it? – Lego490 Sep 08 '20 at 12:52
  • Do you have any other index on this table – Akhilesh Mishra Sep 08 '20 at 13:00
  • I normally 1 other index in this table - index for user_id, as I need to assign each item to a user. However, I removed it for testing purposes and it still seems to show the same thing - a SEQ scan query in EXPLAIN ANALYZE. – Lego490 Sep 08 '20 at 13:06