valgog <valgog@gmail.com> wrote ..
> On Jul 23, 7:00 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> > valgog <val...@gmail.com> writes:
> > > how to build an multicolumn index with one column order ASCENDING and
> > > another column order DESCENDING?
> >
> > Use 8.3 ;-)
> >
> > In existing releases you could fake it with a custom reverse-sorting
> > operator class, but it's a pain in the neck to create one.

I've often gotten what I want by using a calculated index on (f1, -f2). ORDER BY will take an expression, e.g. ORDER BY f1, -f2. Simpler than a custom operator.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

*andrew@pillette.com wrote:
> I've often gotten what I want by using a calculated index on (f1,
> -f2). ORDER BY will take an expression, e.g. ORDER BY f1, -f2.
> Simpler than a custom operator.

Valentine Gogichashvili wrote:
> Yes, this is true, but I do now know how to make text order be
> reversible? There is no - (minus) operator for text value. By now it is
> not a problem for me, but theoretically I do not see other chance to
> reverse text fields order...

(Please use plain text, not HTML, for Usenet posts.)

How about two indexes, one on each column? Then the indexes will cooperate
when combined in a WHERE clause.
<http://www.postgresql.org/docs/8.2/interactive/indexes-bitmap-scans.html>

I don't believe the index makes a semantic difference with regard to ascending
or descending. An index is used to locate records in the selection phase of a
query or modification command.

--
Lew

On Jul 25, 2:14 am, Lew <l...@lewscanon.nospam> wrote:
>
> How about two indexes, one on each column? Then the indexes will cooperate
> when combined in a WHERE clause.
> <http://www.postgresql.org/docs/8.2/interactive/indexes-bitmap-scans.html>
>
> I don't believe the index makes a semantic difference with regard to ascending
> or descending. An index is used to locate records in the selection phase of a
> query or modification command.
>
> --
> Lew

Ordered indexes (b-tree in this case) are also used to get the needed
record order and it is absolutely not necessary to have a WHARE clause
in your select statement to use them when you are using ORDER BY.

--
Valentine

valgog wrote:
> On Jul 25, 2:14 am, Lew <l...@lewscanon.nospam> wrote:
>> How about two indexes, one on each column? Then the indexes will cooperate
>> when combined in a WHERE clause.
>> <http://www.postgresql.org/docs/8.2/interactive/indexes-bitmap-scans.html>
>>
>> I don't believe the index makes a semantic difference with regard to ascending
>> or descending. An index is used to locate records in the selection phase of a
>> query or modification command.
>>
>> --
>> Lew
>
> Ordered indexes (b-tree in this case) are also used to get the needed
> record order and it is absolutely not necessary to have a WHARE clause
> in your select statement to use them when you are using ORDER BY.

But does that affect anything when you "ORDER BY foo ASC" vs. when you "ORDER
BY foo DESC"?

For use by ORDER BY, separate column indexes are an even better idea.

--
Lew