Indexing order by desc

From: Craig Addleman <CraigA(at)SHARECHIVE(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Indexing order by desc
Date: 2003-03-19 17:51:01
Message-ID: 21F455F95EFDD411A3E200508BB22B8016F2E9@sharechive01.sharechive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Our application has a pair of list boxes which enable the user to control
the order of rows returned. By selecting two columns out of a total of 5
possibilities, the result set is ordered by those 2 cols. For example, a
user can order by 'username' and 'udate' or 'username' and 'status'. Without
indexing on pairs of columns, execution time takes far too long. The problem
is with 'order by descending' since we can't create descending indexes.
We have a potential solution: convert dates to negative integers (2003-03-19
becomes -20030319), and convert characters to their alphabet-position
opposites: AbCz becomes zYxA. These converted values will require
additional columns in the table (a materialized view). 100 indexes are
needed in order to index all combinations of the five columns. It's a
kludge, but it looks like it would work. Does anyone have a better solution?

Craig Addleman
DBA
ShareChive LLC
craiga(at)sharechive(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Johnson, Shaunn 2003-03-19 18:23:47 how to make query more efficient?
Previous Message terry 2003-03-19 17:17:51 HELP: pg_clog file not found error