up vote 1 down vote favorite

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.

share|improve this question
    
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 Jun 3 '13 at 14:16
1  
@LukasEder: or he could use an enum, to keep existing queries untouched. – Denis de Bernardy Jun 3 '13 at 14:17
up vote 3 down vote

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

share|improve this answer
1  
Would an index on kind may be enough? Not sure how much the additional columns will speed up the sort. – AngerClown Jun 3 '13 at 14:17
    
@AngerClown There's already an index on kind – Shpigford Jun 3 '13 at 14:18
    
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. – Denis de Bernardy Jun 3 '13 at 14:19
3  
@AngerClown: The plan seems to indicate that 150k rows have kind = 'software', so the index doesn't filter too selectively – Lukas Eder Jun 3 '13 at 14:21
    
@LukasEder It can still help as a part of a composite index. – Igor Romanchenko Jun 3 '13 at 14:24
up vote 0 down vote

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.

share|improve this answer
    
Clustering wont help as the query requires full scan and sort. Postgres memory allocation can help, but not much. – Igor Romanchenko Jun 3 '13 at 14:22
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.