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)

Thanks,
Jamie K.

2010/7/27 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>

> show us explain analyze on this
>