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

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.