Re: possible bug with compound index.

Neil Dugan wrote:
> On Sun, 2005-02-13 at 20:40 -0800, Stephan Szabo wrote:
> > 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).
>
> Thanks Stephan, for the hint on using desc twice.
> 'select * from table order by name desc,serialno desc limit 1'
> does work.
>
> I didn't realise it was separating the order into two sections, I'm
> sorry if this caused any trouble for you. My mistake, bye!

What this brings up is that we have no way to create indexes that have
mixed ascending/descending column specifications.

Should this be a TODO? I am unsure.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073