On Fri, 2006-03-17 at 08:25, andrew@xxxxxxxxxxxx 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
