CREATE INDEX .. ON table1 (field1 asc, field2 desc)

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/

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

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.

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