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).

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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!




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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@candle.pha.pa.us | (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@postgresql.org so that your
message can get through to the mailing list cleanly

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