> I have a performance problem when traversing a table in index order with
> multiple columns including a date column in date reverse order. Below
> follows a simplified description of the table, the index and the
> associated query
>
> \d prcdedit
> prcdedit_prcd | character(20) |
> prcdedit_date | timestamp without time zone |
>
> Indexes:
> "prcdedit_idx" btree (prcdedit_prcd, prcdedit_date)
Depending on how you use the table, there are three possible solutions.
First, if it makes sense in the domain, using an ORDER BY where _both_ columns
are used descending will make PG search the index in reverse and will be just
as fast as when both as searched by the default ascending.
Second possibility: Create a dummy column whose value depends on the negative
of prcdedit_date, e.g., -extract(epoch from prcdedit_date), keep the dummy
column in sync with the original column using triggers, and rewrite your
queries to use ORDER BY prcdedit_prod, dummy_column.
Third: Create an index on a function which sorts in the order you want, and
then always sort using the function index (you could use the -extract(epoch...)
gimmick for that, among other possibilities.)
HTH.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
