Skip site navigation (1) Skip section navigation (2)

Re: Order-by and indexes

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Order-by and indexes
Date: 2011-06-29 15:38:30
Message-ID: 20110629173830.0a63992d@anubis.defcon1 (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Wed, 29 Jun 2011 17:19:00 +0200, Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no> wrote:

> The ordering of the fk doesn't matter to me now.

It should: it gives the condition that let you get the latest fk...

> Yes, there might be (and
> are) several ones with the same value for this. I just want the latest added
> one that matches. And I don't understand why this is not always the first
> one matching a forward scan, as new entries are put in front?

Because, according to the docs, such a query as you told about *cannot*
guaranty the order of the rows (logical: you ask for all fk=111 but nothing
except sorting on id can insure you'll have fk rows in the right order.)

> > Also I don't
> > understand why the order by query is scanning backwards, when the record I
> > want is in the other end?

Take a sheet of paper and a pencil, write the whole shebang down, make this
model run'by'hand and you'll see why.

>  Because id is the primary key (I guess:) and ordering DESC puts id latest
> > rows first in list, so limiting select to 1 returns the last one.

Anyway, Tom gave you the answer to speed up your query.

<stu> Stupid nick highlighting
<stu> Whenever someone starts with "stupid" it highlights the nick.  Hmm.
		-- #Debian

In response to


pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2011-06-29 15:42:00
Subject: Re: Locking out a user after several failed login attempts
Previous:From: Mike ThomsenDate: 2011-06-29 15:26:46
Subject: Re: Locking out a user after several failed login attempts

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group