How can I optimize this SQL query in Postgres?

Question

I've got a pretty large table with nearly 1 million rows and some of the queries are taking a long time (over a minute).

Here is one that's giving me a particularly hard time...

EXPLAIN ANALYZE SELECT "apps".* FROM "apps" WHERE "apps"."kind" = 'software' ORDER BY itunes_release_date DESC, rating_count DESC LIMIT 12;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=153823.03..153823.03 rows=12 width=2091) (actual time=162681.166..162681.194 rows=12 loops=1)
   ->  Sort  (cost=153823.03..154234.66 rows=823260 width=2091) (actual time=162681.159..162681.169 rows=12 loops=1)
         Sort Key: itunes_release_date, rating_count
         Sort Method: top-N heapsort  Memory: 48kB
         ->  Seq Scan on apps  (cost=0.00..150048.41 rows=823260 width=2091) (actual time=0.718..161561.149 rows=808554 loops=1)
               Filter: (kind = 'software'::text)
 Total runtime: 162682.143 ms
(7 rows)

So, how would I optimize that? PG version is 9.2.4, FWIW.

There are already indexes on kind and kind, itunes_release_date.

This doesn't answer your question, but if you have 1 million records, you probably better create an app_kind table with numeric references from apps, rather than repeating varchars such as 'software' all over — Lukas Eder
– Lukas Eder, Commented Jun 3, 2013 at 14:16
@LukasEder: or he could use an enum, to keep existing queries untouched. — Denis de Bernardy
– Denis de Bernardy, Commented Jun 3, 2013 at 14:17

Denis de Bernardy · Accepted Answer · 2013-06-03 14:13:01Z

3

Looks like you're missing an index, e.g. on (kind, itunes_release_date desc, rating_count desc).

answered Jun 3, 2013 at 14:13

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

AngerClown Over a year ago

Would an index on kind may be enough? Not sure how much the additional columns will speed up the sort.

Denis de Bernardy Over a year ago

An index on kind can be useful but will still yield a top-n sort. To make use of an index to get the top-12 directly, OP will need to add (all of) the sort columns in the index too.

Lukas Eder Over a year ago

@AngerClown: The plan seems to indicate that 150k rows have kind = 'software', so the index doesn't filter too selectively

Ihor Romanchenko Over a year ago

@LukasEder It can still help as a part of a composite index.

ypercubeᵀᴹ Over a year ago

@LukasEder The index will help to retrieve the limited rows, without sorting the whole table (or the whole 150k rows).

|

AngerClown · Accepted Answer · 2013-06-03 14:20:27Z

0

How big is the apps table? Do you have at least this much memory allocated to postgres? If it's having to read from disk every time, query speed will be much slower.

Another thing that may help is to cluster the table on the 'apps' column. This may speed up disk access since all the software rows will be stored sequentially on disk.

answered Jun 3, 2013 at 14:20

AngerClown

6,2391 gold badge27 silver badges29 bronze badges

1 Comment

Ihor Romanchenko Over a year ago

Clustering wont help as the query requires full scan and sort. Postgres memory allocation can help, but not much.

Ihor Romanchenko · Accepted Answer · 2013-06-03 14:20:43Z

0

The only way to speed up this query is to create a composite index on (itunes_release_date, rating_count). It will allow Postgres to pick first N rows from the index directly.

answered Jun 3, 2013 at 14:20

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Collectives™ on Stack Overflow

How can I optimize this SQL query in Postgres?

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related