How can I speed up with query?

From: "Scott Morrison" <smorrison(at)navtechinc(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: How can I speed up with query?
Date: 2003-02-05 18:59:30
Message-ID: DHEKKBMAPBCGPOFEBIFFEEJACBAA.smorrison@navtechinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I would like to know if there is a more efficient way to perform a query.

I have a table (see below) with an ID, a date, and a value. The date
specifies when the entry was added. I want to query the table to determine
what the value was on a specific day.

I have the following query, which works quite well but it is very slow (the
real table I'm using has 200000 records)... I'm sure the slowness is due to
the subselect... does anybody know any way to make this query faster?

Thanks in advance,
Scott

My current query:
select a.* from sample a where (id,date) in (select a.id,max(date) from
sample where date<='<<the date>>' and id=a.id) order by id;

So with the data below, filling in '2003-02-01' for <<the date>> gives:
id | date | value
----+------------+-------
1 | 2003-02-01 | 12
2 | 2003-02-01 | 9
3 | 2003-02-01 | 2
4 | 2003-02-01 | 11

And filling in '2003-02-04' for <<the date>> gives:
id | date | value
----+------------+-------
1 | 2003-02-04 | 21
2 | 2003-02-01 | 9
3 | 2003-02-01 | 2
4 | 2003-02-03 | 12

Here is the table layout and the sample data I'm using:

Table "sample"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
date | date | not null
value | integer | not null
Primary key: sample_pkey

id | date | value
----+------------+-------
1 | 2003-02-01 | 12
1 | 2003-02-02 | 16
1 | 2003-02-04 | 21
2 | 2003-02-01 | 9
3 | 2003-02-01 | 2
4 | 2003-02-01 | 11
4 | 2003-02-03 | 12
(7 rows)

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Chad Thompson 2003-02-05 19:19:06 Re: How can I speed up with query?
Previous Message Claire De Longchamp 2003-02-05 18:41:37 Re: Inserting / selecting rows with TIMESTAMP