How can I speed up with query?

Поиск
Список
Период
Сортировка
От Scott Morrison
Тема How can I speed up with query?
Дата
Msg-id DHEKKBMAPBCGPOFEBIFFEEJACBAA.smorrison@navtechinc.com
обсуждение исходный текст
Ответы Re: How can I speed up with query?  (Andrew McMillan <andrew@catalyst.net.nz>)
Список 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)


В списке pgsql-novice по дате отправления:

Предыдущее
От: Claire De Longchamp
Дата:
Сообщение: Re: Inserting / selecting rows with TIMESTAMP
Следующее
От: "Chad Thompson"
Дата:
Сообщение: Re: How can I speed up with query?