CODEWITHSUNDEEP
sqlperformancepostgresqlquery-optimizationpostgresql-performance
chirag7jain
chirag7jain

Reputation: 1537

Query performance improvements -- Sort Filters

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.

Upvotes: 0

Views: 130

Answers (2)

sagar pant
sagar pant

Reputation: 357

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?

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

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?

Upvotes: 1

Related Questions