From: | Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> |
---|---|
To: | Poul Møller Hansen <freebsd(at)pbnet(dot)dk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Optimizing query |
Date: | 2005-08-15 10:11:09 |
Message-ID: | Pine.LNX.4.44.0508151209190.2995-100000@zigo.dhs.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 15 Aug 2005, Poul Møller Hansen wrote:
> I have a problem creating a usable index for the following simple query:
> SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1
>
> id is a serial, so the query is to find the latest entry to a given node
> and id is the primary key.
> The table contains around 1 million records and the query takes around 2
> seconds.
>
> I have tried to make an index on node and also on both id & node, but is
> doesn't lower the
> query time.
Try to make an index on (node,id) and write the query as:
SELECT * FROM my.table WHERE node = '10' ORDER BY node desc, id desc LIMIT 1;
Then i'm pretty sure it will use that index.
--
/Dennis Björklund
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis Bjorklund | 2005-08-15 10:19:07 | Re: atomic function |
Previous Message | Mage | 2005-08-15 10:09:30 | atomic function |