From: andrew@pillette.com
To: theo@flame.co.za
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexes with descending date columns
Date: Thu, 16 Mar 2006 22:25:19 -0800
Message-ID: <200603170625.k2H6PJ926955@pillette.com> (raw)
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@pillette.com, theo@flame.co.za
Subject: Re: Indexes with descending date columns
In-Reply-To: <200603170625.k2H6PJ926955@pillette.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox