I'm trying to increase my query speed for this table, it currently takes 2-3 seconds and would like to make if faster if possible.
my table reads as
Table "public.campaign_impressions"
Column | Type | Modifiers
-----------+-----------------------------+---------------
campaign | integer | not null
page | integer | not null
timestamp | timestamp without time zone | default now()
Indexes:
"campaign_impressions_campaign_idx" btree (campaign)
"campaign_impressions_page_idx" btree (page)
"campaign_impressions_timestamp_idx" btree ("timestamp")
This is the type of query I do on the table (get the page and # of times for each page this campaign was viewed between date x & y)
SELECT page, COUNT(page) AS impressions
FROM campaign_impressions
WHERE campaign = 1
AND timestamp BETWEEN '2010-05-21 00:00:00' AND '2010-07-27 00:00:00'
GROUP BY page
ORDER BY impressions
right now I have ~13 million rows in the table, and the query seems (to me) easy enough. but it feels like 2-3 seconds is a long time. Is there any way I can squeeze more speed out of this? Is there a better way to store the data for the type of info I'm trying to extract? I'm open to all suggestions
Thanks,
Jamie K.