On Fri, 2006-03-17 at 08:25, [EMAIL PROTECTED] wrote:
> > 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.
All good input - thanks, however, before I start messing with my stuff
which I know will be complex - some questions to any of the developers
on the list.
i Is it feasible to extend index creation to support descending
columns? ... this is supported on other commercial and non
commercial databases, but I do not know if this is a SQL standard.
ii If no to i, is it feasible to extend PostgreSQL to allow traversing
an index in column descending and column ascending order - assuming
an order by on more than one column with column order not
in the same direction and indexes existing? ... if that makes sense.
--
Regards
Theo
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
