Re: possible bug with compound index.

On Mon, 14 Feb 2005, Neil Dugan wrote:

> I am using PostgreSQL 7.4.7
> I have a table with serveral fields two of these are a serialno
> (bigserial) and name(varchar). I have created two indexs on these
> fields.
> 1) on name
> 2) on name,serialno
> if I use the command
> 'select * from table order by name limit 1'
> everything is OK
> if I use the command
> 'select * from table order by name desc limit 1'
> everything is OK
> if I use the command
> 'select * from table order by name,serialno limit 1'
> everything is OK
> if I use the command
> 'select * from table order by name,serialno desc limit 1'
> The command is SLOW and gives back the INCORRECT data.

Without any example data and result, it's hard to say what you were
expecting or got. I'd expect the highest numbered serialno record for the
lowest sorting name from the above which is what any tests I've tried do.

Right now I believe it won't consider index usage because the ordering
asked for doesn't match either a forward ordering of the index(name,
serialno) or a reverse order (name desc, serialno desc).