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>
> > By default, B-tree indexes store their entries in ascending order with
> > last. This means that a forward scan of an index on column x produces
> > 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
> > DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST
> > 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
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
pgsql-novice by date
|Next:||From: Mike Thomsen||Date: 2011-06-29 15:26:46|
|Subject: Re: Locking out a user after several failed login attempts|
|Previous:||From: Vincent Ficet||Date: 2011-06-29 15:11:52|
|Subject: pg_advisory_locks in a multithreaded application context|