Re: Multiple Order By Criteria

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of J(at)Planeti(dot)Biz
Sent: Rabu, 18 Januari 2006 07:23
To: Stephan Szabo
Cc: Josh Berkus; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Multiple Order By Criteria

I've read all of this info, closely. I wish when I was searching for an
answer for my problem these pages came up. Oh well.
Well, I think you have to know about btree index. Btree is good enough,
although it's not better. It will perform best, if it doesn't index
too many multiple column.
In your case, you have to consentrate on 2 or 3 fields that will
use frequently. Put the most duplicate value on the front and others
are behind.
Eq:
receipt, carrier_id, batchnum is the most frequently use,
but the most duplicate value are: carrier_id, receipt, and batchnum
so make btree index (carrier_id, receipt, batchnum).
Btree will not suffer, and we also will advantage if the table
have relationship with other table with the same fields order. We have
not to make another index for that relation.

Best regards,
ahmad fajar.

> I am getting an idea of what I need to do to make this work well. I was
> wondering if there is more information to read on how to implement this
> solution in a more simple way. Much of what's written seems to be towards
> audience that should understand certain things automatically.
----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: <J(at)planeti(dot)biz>
Cc: "Josh Berkus" <josh(at)agliodbs(dot)com>; <pgsql-performance(at)postgresql(dot)org>
Sent: Tuesday, January 17, 2006 6:39 PM
Subject: Re: [PERFORM] Multiple Order By Criteria

>
> On Tue, 17 Jan 2006 J(at)Planeti(dot)Biz wrote:
>
>> I created the index like this:
>>
>> CREATE INDEX rcbee_idx
>> ON detail_summary
>> USING btree
>> (receipt, carrier_id, batchnum, encounternum, encounter_id);
>>
>> Is this correct ?
>
> That would work if you were asking for all the columns ascending or
> descending, but we don't currently use it for mixed orders.
>
>> How do I make a reverse opclass ?
>
> There's some information at the following:
> http://archives.postgresql.org/pgsql-novice/2005-10/msg00254.php
> http://archives.postgresql.org/pgsql-general/2005-01/msg00121.php
> http://archives.postgresql.org/pgsql-general/2004-06/msg00565.php
>