Increase Query Speed

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.

Responses

Browse pgsql-general by date

  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