What index will fix this query?

Question

How can I fix the sequential scan over 'items'? I already have an index for [league_id, buyout_amount]

"Limit  (cost=72399.08..72399.15 rows=25 width=553) (actual time=2037.893..2037.898 rows=25 loops=1)"
"  ->  Sort  (cost=72399.08..72817.16 rows=167231 width=553) (actual time=2037.891..2037.894 rows=25 loops=1)"
"        Sort Key: normalized_buyout, id"
"        Sort Method: top-N heapsort  Memory: 41kB"
"        ->  Seq Scan on items  (cost=0.00..67679.95 rows=167231 width=553) (actual time=0.764..1906.766 rows=183374 loops=1)"
"              Filter: ((buyout_amount <> 0::numeric) AND (league_id = 1))"
"Total runtime: 2037.949 ms"

This is the query

SELECT "items".* FROM "items" 
WHERE (items.league_id = '1') AND (buyout_amount <> 0) 
ORDER BY normalized_buyout ASC, items.id DESC 
LIMIT 25

Current indexes (that are relevant to this query)

-- Index: index_items_on_buyout_amount

-- DROP INDEX index_items_on_buyout_amount;

CREATE INDEX index_items_on_buyout_amount
  ON items
  USING btree
  (buyout_amount);

-- Index: index_items_on_id

-- DROP INDEX index_items_on_id;

CREATE INDEX index_items_on_id
  ON items
  USING btree
  (id DESC);

-- Index: index_items_on_id_and_league_id

-- DROP INDEX index_items_on_id_and_league_id;

CREATE INDEX index_items_on_id_and_league_id
  ON items
  USING btree
  (id, league_id);

-- Index: index_items_on_id_and_normalized_buyout

-- DROP INDEX index_items_on_id_and_normalized_buyout;

CREATE INDEX index_items_on_id_and_normalized_buyout
  ON items
  USING btree
  (id DESC, normalized_buyout);


-- Index: index_items_on_league_id

-- DROP INDEX index_items_on_league_id;

CREATE INDEX index_items_on_league_id
  ON items
  USING btree
  (league_id);

-- Index: index_items_on_league_id_and_buyout_amount

-- DROP INDEX index_items_on_league_id_and_buyout_amount;

CREATE INDEX index_items_on_league_id_and_buyout_amount
  ON items
  USING btree
  (league_id, buyout_amount);

-- Index: index_items_on_league_id_and_id

-- DROP INDEX index_items_on_league_id_and_id;

CREATE INDEX index_items_on_league_id_and_id
  ON items
  USING btree
  (league_id, id DESC);

-- Index: index_items_on_normalized_buyout

-- DROP INDEX index_items_on_normalized_buyout;

CREATE INDEX index_items_on_normalized_buyout
  ON items
  USING btree
  (normalized_buyout);

what are the datatypes of `league_id` and `buyout_amount`, and what indexes have you already defined? — Bohemian, Mar 04 '14 at 03:52
`random_page_cost`, `seq_page_cost`? Also, an index on `normalized_buyout ASC, items.id DESC` may be useful. — Craig Ringer, Mar 04 '14 at 04:01
Page costs at at default values, which are 1 and 4 respectively. I've got that index Craig, which is making the order really fast. It's just the filtering that its deciding to do a seq scan on — Nick Barrett, Mar 04 '14 at 04:17
Do you really need to select items.* ? It may be the optimiser thinks that most rows have buyout_amount<> 0 (or most have league_id = 1) and since you want all the columns, it might as well go through the whole table and just discard the few non-matches. [caution: I'm not a postgresql expert]. — Turophile, Mar 04 '14 at 04:34
Comments [in this post](http://stackoverflow.com/questions/19437816/why-does-postgres-do-a-sequential-scan-where-the-index-would-return-1-of-the?rq=1) suggest to me that the problem may be caused by `ORDER BY` + `FIRST 25`. Try removing either/both to see if it affects the access path. — Turophile, Mar 04 '14 at 04:44

score 0 · Accepted Answer · answered Mar 04 '14 at 06:19

0

I discovered my ORDER BY index wasn't being used. By adding a partial index WHERE buyout_amount <> 0 ORDER BY normalized_buyout, id DESC it started using the index.

answered Mar 04 '14 at 06:19

Nick Barrett

1,051
2
11
28

score 0 · Answer 2 · answered Mar 04 '14 at 09:18

Postgres can be particularly sensitive/stupid about casts: if you don't use constants that are the same type as the column, it (usually in my experience) casts the column to the constant, thus invalidating indexes.

Make sure your constants align with the column types.

Change

WHERE items.league_id = '1' -- constant is text
AND buyout_amount <> 0 -- constant is int

To:

WHERE items.league_id = 1 -- constant is int
AND buyout_amount <> 0.0 -- constant is decimal

What index will fix this query?

2 Answers2