Re: Increase Query Speed

EXPLAIN ANALYZE SELECT page, count(page) as impressions FROM campaign_impressions WHERE campaign = 42 and "timestamp" BETWEEN '2010-05-21 00:00:00' AND '2010-07-27 00:00:00' group by page order by impressions;

                                                                                   QUERY PLAN                                                                                    

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Sort  (cost=106059.36..106059.40 rows=16 width=4) (actual time=2209.808..2209.816 rows=109 loops=1)

   Sort Key: (count(page))

   Sort Method:  quicksort  Memory: 30kB

   ->  HashAggregate  (cost=106058.84..106059.04 rows=16 width=4) (actual time=2209.749..2209.765 rows=109 loops=1)

         ->  Bitmap Heap Scan on campaign_impressions  (cost=19372.78..102534.06 rows=704956 width=4) (actual time=424.023..1980.987 rows=1010896 loops=1)

               Recheck Cond: (campaign = 42)

               Filter: (("timestamp" >= '2010-05-21 00:00:00'::timestamp without time zone) AND ("timestamp" <= '2010-07-27 00:00:00'::timestamp without time zone))

               ->  Bitmap Index Scan on campaign_impressions_campaign_idx  (cost=0.00..19196.54 rows=1039330 width=0) (actual time=421.587..421.587 rows=1044475 loops=1)

                     Index Cond: (campaign = 42)

 Total runtime: 2209.869 ms

(10 rows)