Thread: possible bug with compound index.
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.
Doing an 'explain' on the above query says that the index wasn't used.
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).
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!
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
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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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 we already had a TODO to provide reverse-sort operator classes
in the standard distribution. (In the meantime those that need this can
make up their own; see past discussions in the archives.)
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > 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 we already had a TODO to provide reverse-sort operator classes > in the standard distribution. (In the meantime those that need this can > make up their own; see past discussions in the archives.) It wasn't there. Added to TODO: * Allow the creation of indexes with mixed ascending/descending specifiers -- 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
I just went from compiling PostgreSQL 8.0.1 and all was fine, but when I launched initdb -D /opt/postgresql-8.0.1/lib/data/ --encoding=LATIN1 --locale=es_ES all that I got was FATAL: could not open file "$libdir/ascii_and_mic" I dirtly changed $libdir for /opt/postgresql-8.0.1/lib in /opt/postgresql-8.0.1/share/conversion_create.sql and all ran smoothly, but I think this is not the right way. Have you had this behaviour too? (Slackware 10.1) -- ----------------------- Alberto Cabello Sánchez alberto@unex.es Servicio de Informática 924 289 318 -----------------------