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

Re: Order-by and indexes

From: Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no>
To: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Order-by and indexes
Date: 2011-06-29 15:19:00
Message-ID: BANLkTin_sitokWkuaA7Wq0M-+V5kpKH-bQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
2011/6/29 Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com>

> On Wed, 29 Jun 2011 16:42:36 +0200, Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no>
> wrote:
>
> ...
> > By default, B-tree indexes store their entries in ascending order with
> nulls
> > last. This means that a forward scan of an index on column x produces
> output
> > satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The
> > index can also be scanned backward, producing output satisfying ORDER BY
> x
> > DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST
> is
> > the default for ORDER BY DESC).
> >
> > Doesn't this mean that when I'm not using the order by clause, and it
> uses a
> > Index Scan, I will always get the latest value in return?
>
> Yes, but you're ordering by column id while you seek value into column fk.
>
> This is very different from the doc you quote: in your case, ordering by id
> returns you all values of fk BUT the ordering of fk is absolutely
> undefined.

And, as I suppose fk stands for Foreign Key, you have many row using same
> values for fk (?)
>

The ordering of the fk doesn't matter to me now. 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?


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

 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.


Then why is it slow?

In response to

Responses

pgsql-novice by date

Next:From: Mike ThomsenDate: 2011-06-29 15:26:46
Subject: Re: Locking out a user after several failed login attempts
Previous:From: Vincent FicetDate: 2011-06-29 15:11:52
Subject: pg_advisory_locks in a multithreaded application context

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