Now that expressions can be used in indexes in 7.4 you can have multicolumn
indexes that are ordered in different directions. However the planner
doesn't seem to understand that order by -col asc is the same as order by
col desc (for at least the normal -) so you have to be careful how you
write queries when doing this.
For example:
bruno=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
col1 | integer |
col2 | integer |
Indexes:
"test1" btree (col1, ((- col2)))
bruno=> explain select col1, col2 from test order by col1 asc, col2 desc;
QUERY PLAN
----------------------------------------------------------------
Sort (cost=814.39..839.39 rows=10000 width=8)
Sort Key: col1, col2
-> Seq Scan on test (cost=0.00..150.00 rows=10000 width=8)
(3 rows)
bruno=> explain select col1, col2 from test order by col1 asc, -col2 asc;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using test1 on test (cost=0.00..337.50 rows=10000 width=8)
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
