2011/6/29 Jean-Yves F. Barbier
<12ukwn@gmail.com> On Wed, 29 Jun 2011 16:42:36 +0200, Odd Hogstad <odd.hogstad@smartm.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?