Message-ID: <43005CBD.5070703@archonet.com>
Date: Mon, 15 Aug 2005 10:13:33 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= <freebsd@pbnet.dk>
Cc: pgsql-general@postgresql.org
Subject: Re: Optimizing query
References: <43005482.7020105@pbnet.dk>
In-Reply-To: <43005482.7020105@pbnet.dk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Poul M=F8ller Hansen wrote:
> I have a problem creating a usable index for the following simple query=
:
> SELECT * FROM my.table WHERE node =3D '10' ORDER BY id DESC LIMIT 1
>=20
> id is a serial, so the query is to find the latest entry to a given nod=
e=20
> and id is the primary key.

You're not necessarily getting the latest entry, just the one with the=20
highest "id". Sequences guarantee uniqueness but if you have concurrent=20
inserts not necessarily ordering.

> The table contains around 1 million records and the query takes around =
2=20
> seconds.


Well, you don't say how many different values for "node" there are, nor=20
how many rows you would expect where node=3D'10'.

> I have tried to make an index on node and also on both id & node, but i=
s=20
> doesn't lower the query time.

Difficult to say what's happening since you don't supply any EXPLAIN=20
ANALYSE output.

However, if you have an index on (node,id) you might want to try:
   SELECT ... ORDER BY node DESC, id DESC LIMIT 1;
That way the "ORDER BY" part clearly tells the planner that a=20
reverse-order on your index will be useful.

--
   Richard Huxton
   Archonet Ltd