agora inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Richard Huxton <dev@archonet.com>
To: Poul Møller Hansen <freebsd@pbnet.dk>
Cc: pgsql-general@postgresql.org
Subject: Re: Optimizing query
Date: Mon, 15 Aug 2005 10:13:33 +0100
Message-ID: <43005CBD.5070703@archonet.com> (raw)
In-Reply-To: <43005482.7020105@pbnet.dk>
References: <43005482.7020105@pbnet.dk>

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.

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

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


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

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

Difficult to say what's happening since you don't supply any EXPLAIN 
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 
reverse-order on your index will be useful.

--
   Richard Huxton
   Archonet Ltd

view thread (41+ messages)  latest in thread

Message-ID: <43005CBD.5070703@archonet.com>
Permalink:  ../43005CBD.5070703@archonet.com/
Also on:    postgresql.org/message-id/43005CBD.5070703@archonet.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-general@postgresql.org
  Cc: dev@archonet.com, freebsd@pbnet.dk
  Subject: Re: Optimizing query
  In-Reply-To: <43005CBD.5070703@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox