How can improve the performance of this query:
SELECT designs.* FROM designs
WHERE designs.state = 'in_stock'
ORDER BY designs.grade, id DESC
Explain output:
Sort (cost=47475.35..47591.91 rows=46621 width=763)
Sort Key: grade, id
-> Seq Scan on designs (cost=0.00..12304.20 rows=46621 width=763)
Filter: ((state)::text = 'in_stock'::text)
The table has over 250000 records.
Building an index on state doesn't help much.
1) Name the columns instead of * such as Select col1, col2....Do you need all columns to be in the resultset? If not show only these columns that you need.
2) Which columns did you put index on? Try indexing on state and include grade and ID.
3) Can state = 'in_stock ' be replaced by something that filters on integer value and produce the same result? Can the state field itself be converted to type int and indexed and then create a lookup table for these integer values?
To optimize for the given query (which may not be the best strategy overall), use a partial index with matching sort order:
CREATE INDEX foo_idx ON designs (grade, id DESC) WHERE state = 'in_stock';
Actually I would start with your table layout, looking at the data type of state first.
Then I would ask which columns and rows you actually need to return from your query. I doubt you need all columns of all 46621 rows in the result for display?
CREATE INDEX design_order_idx ON designs (grade, id);[postgresql-performance].