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?
|
Список | 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 по дате отправления: