Skip site navigation (1) Skip section navigation (2)

Re: Indexes with descending date columns

From: andrew(at)pillette(dot)com
To: theo(at)flame(dot)co(dot)za
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Indexes with descending date columns
Date: 2006-03-17 06:25:19
Message-ID: 200603170625.k2H6PJ926955@pillette.com (view raw or flat)
Thread:
Lists: pgsql-performance
> 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.

Responses

pgsql-performance by date

Next:From: Luke LonerganDate: 2006-03-17 06:44:25
Subject: Re: 1 TB of memory
Previous:From: Josh BerkusDate: 2006-03-17 05:43:25
Subject: Re: 1 TB of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group