Re: How can I speed up with query?

On Thu, 2003-02-06 at 07:59, Scott Morrison wrote:
>
> 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;

max(date) will require a scan on the recordset in the subselect.  You
might be better to use 'ORDER BY date DESC LIMIT 1' which will use an
index on date if one exists.

SELECT * FROM sample a WHERE (id, date)
  IN (SELECT a.id, date FROM sample WHERE id=a.id ORDER BY date DESC
LIMIT 1)

When you had 20,000 records you would definitely be wanting an index on
date and an index on id.

I think that the LIMIT clause on subselects is only implemented in
recent PostgreSQL however - 7.2 on, I believe.

To examine the query plans that PostgreSQL comes up with, and help you
choose a better structure for your query, you should use "EXPLAIN <<the
query>>" .

Regards,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------