Re: - Mailing list pgsql-novice

From Tom Lane
Subject Re:
Date
Msg-id 15507.1044564247@sss.pgh.pa.us
Whole thread Raw
In response to  ("Scott Morrison" <smorrison@navtechinc.com>)
List pgsql-novice
"Scott Morrison" <smorrison@navtechinc.com> writes:
> 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.

Try something like

    SELECT ... FROM table
    WHERE date <= 'target date'
    ORDER BY date DESC
    LIMIT 1;

Given an index on the date column, this should be very fast.

If you need additional constraints (like the 'id' in your example),
you can try

    SELECT ... FROM table
    WHERE id = 'target id' AND date <= 'target date'
    ORDER BY id DESC, date DESC
    LIMIT 1;

where now you need an index declared on (id, date)  (in that order).
The basic idea is to make sure that the row you want is the first one
reached when scanning from a given boundary in the index.

            regards, tom lane

pgsql-novice by date:

Previous
From: Doug Silver
Date:
Subject: Re:
Next
From: "Scott Morrison"
Date:
Subject: Re: