Re: Order-by and indexes

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?