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@xxxxxxxxxxxxxxxx | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
