Re: - Mailing list pgsql-novice

On Tuesday 04 February 2003 03:35 pm, Scott Morrison wrote:
> 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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

First, you didn't say if you've indexed the date field, which you should do if
you're searching on that field more than once.  That will certainly help out
with the speed.  Also, that query seems a bit overdone, how about this:

select * from sample where date<='<<the date>>' order by id,date;

You're only getting information from a single table, so I don't think the
subselect was necessary, though I might be missing something since the coffee
has worn off ;)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Urchin Software Corp.    http://www.urchin.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~