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

On Sat, 24 Aug 2002, Bruno Wolff III wrote:

> On Sat, Aug 24, 2002 at 20:18:25 +0500,
> Timur <itvthor(at)sdf(dot)lonestar(dot)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)