Re: How can I speed up with query?

How bout

select a.*
from sample a
where exists
(select id
  from sample
  where date <= '<<thedate>>'
  and id = a.id
  order by date desc
  limit 1)
order by id

Is that what your after? Seems like you could do this w/o the subquery.. but
Im not sure exactly what you want.

Thanks
Chad
----- Original Message -----
From: "Scott Morrison" <smorrison@navtechinc.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, February 05, 2003 11:59 AM
Subject: [NOVICE] How can I speed up with query?


> 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)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>