Обсуждение: CREATE INDEX .. ON table1 (field1 asc, field2 desc)
Hello! I am new to PostgreSQL and databases in general, and I have this question: why it is not possible to specify ordering when creating an index? Let's say I have a query which looks like this: SELECT * FROM table1 ORDER BY field1, field2 DESC LIMIT 100; Apparently, system won't use index ON (field1, field2) .. I think it is pretty simple and hope there is a way to create index with field2 indexed in descending order.. Thanks in advance. Timur V. Irmatov.
On Sat, Aug 24, 2002 at 20:18:25 +0500, Timur <itvthor@sdf.lonestar.org> wrote: > Hello! > > I am new to PostgreSQL and databases in general, and I have this > question: why it is not possible to specify ordering when creating an > index? > > Let's say I have a query which looks like this: > SELECT * FROM table1 > ORDER BY field1, field2 DESC > LIMIT 100; > > Apparently, system won't use index ON (field1, field2) .. > > I think it is pretty simple and hope there is a way to create index > with field2 indexed in descending order.. Not that I could tell by looking at the documentation. The simplest way do get the effect would probably be to create a function that can be used to order the data. You can create an index on that function and use that function in order by clauses. Another approach that might work is to create a new operator class that will effectively sort data in reverse order. I don't know enough about creating operator classes to know for sure whether it would be possible or how hard it would be to do.
On Sat, 24 Aug 2002, Bruno Wolff III wrote: > On Sat, Aug 24, 2002 at 20:18:25 +0500, > Timur <itvthor@sdf.lonestar.org> wrote: > > Hello! > > > > I am new to PostgreSQL and databases in general, and I have this > > question: why it is not possible to specify ordering when creating an > > index? > > > > Let's say I have a query which looks like this: > > SELECT * FROM table1 > > ORDER BY field1, field2 DESC > > LIMIT 100; > > > > Apparently, system won't use index ON (field1, field2) .. > > > > I think it is pretty simple and hope there is a way to create index > > with field2 indexed in descending order.. > > Not that I could tell by looking at the documentation. The simplest way > do get the effect would probably be to create a function that can be used > to order the data. You can create an index on that function and use that > function in order by clauses. > > Another approach that might work is to create a new operator class that > will effectively sort data in reverse order. I don't know enough about > creating operator classes to know for sure whether it would be possible > or how hard it would be to do. Well, in 7.3 it appears to be pretty easy, although it's probably tricky in previous versions. I haven't tried it fully, but I needed to make a simple function that did the comparison and set up the operators for the opclass as the reverse of normal and explain shows it using the index for a query like the above. In previous versions you should be able to do the same but you need to hack at system tables to do so (7.3 has a handy create operator class)
En Sat, 24 Aug 2002 20:18:25 +0500 Timur <itvthor@sdf.lonestar.org> escribió: Hello, > I am new to PostgreSQL and databases in general, and I have this > question: why it is not possible to specify ordering when creating an > index? Because there is no point in doing so. Postgres automatically scans an index in reverse direction if you want to use descending order. -- Alvaro Herrera (<alvherre[a]atentus.com>) "Linux transformó mi computadora, de una `máquina para hacer cosas', en un aparato realmente entretenido, sobre el cual cada día aprendo algo nuevo" (Jaime Salinas)
On Sat, Aug 24, 2002 at 08:18:25PM +0500, Timur wrote: > Hello! > > I am new to PostgreSQL and databases in general, and I have this > question: why it is not possible to specify ordering when creating an > index? > > Let's say I have a query which looks like this: > SELECT * FROM table1 > ORDER BY field1, field2 DESC > LIMIT 100; > > Apparently, system won't use index ON (field1, field2) .. It will if you do: SELECT * FROM table1 ORDER BY field1 DESC, field2 DESC LIMIT 100; Time to go read that SQL book again? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Sun, Aug 25, 2002 at 10:00:56 +1000, Martijn van Oosterhout <kleptog@svana.org> wrote: > On Sat, Aug 24, 2002 at 08:18:25PM +0500, Timur wrote: > > Hello! > > > > I am new to PostgreSQL and databases in general, and I have this > > question: why it is not possible to specify ordering when creating an > > index? > > > > Let's say I have a query which looks like this: > > SELECT * FROM table1 > > ORDER BY field1, field2 DESC > > LIMIT 100; > > > > Apparently, system won't use index ON (field1, field2) .. > > It will if you do: > SELECT * FROM table1 > ORDER BY field1 DESC, field2 DESC > LIMIT 100; > > Time to go read that SQL book again? That may or may not be his problem. If the order he wants is order by field1 ASC, fiels2 DESC there isn't a simple way to create a matching index.
Alvaro Herrera <alvherre@atentus.com> writes:
>> I am new to PostgreSQL and databases in general, and I have this
>> question: why it is not possible to specify ordering when creating an
>> index?
> Because there is no point in doing so. Postgres automatically scans an
> index in reverse direction if you want to use descending order.
But he wants, or says he wants, ASC order on the first column and DESC
on the second. I concur with Martijn that this might just be a
beginner's mistake in giving the command ... but if that's really what
he wants then it doesn't correspond to either forward or reverse scan
of a normal index. You would indeed need to make a custom opclass for
the second column in order to make an index that can be scanned in this
order.
regards, tom lane
Is this a TODO item? --------------------------------------------------------------------------- Bruno Wolff III wrote: > On Sun, Aug 25, 2002 at 10:00:56 +1000, > Martijn van Oosterhout <kleptog@svana.org> wrote: > > On Sat, Aug 24, 2002 at 08:18:25PM +0500, Timur wrote: > > > Hello! > > > > > > I am new to PostgreSQL and databases in general, and I have this > > > question: why it is not possible to specify ordering when creating an > > > index? > > > > > > Let's say I have a query which looks like this: > > > SELECT * FROM table1 > > > ORDER BY field1, field2 DESC > > > LIMIT 100; > > > > > > Apparently, system won't use index ON (field1, field2) .. > > > > It will if you do: > > SELECT * FROM table1 > > ORDER BY field1 DESC, field2 DESC > > LIMIT 100; > > > > Time to go read that SQL book again? > > That may or may not be his problem. If the order he wants is > order by field1 ASC, fiels2 DESC there isn't a simple way to create > a matching index. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- 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