From: | Jamie Kahgee <jamie(dot)kahgee(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Increase Query Speed |
Date: | 2010-07-27 12:18:20 |
Message-ID: | AANLkTimEZqgRa943pFZqZNkgPz6FB2Q+TaFmPwGQ-2S_@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ravi Katkar | 2010-07-27 12:43:58 | Re: resultset metadata libpq |
Previous Message | Ravi Katkar | 2010-07-27 12:15:03 | resultset metadata libpq |