Search Postgresql Archives

Re: possible bug with compound index.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, 2005-02-14 at 12:55, Bruce Momjian wrote:
> 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.

I thought it was a todo

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux