-----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of J@xxxxxxxxxxx Sent: Rabu, 18 Januari 2006 07:23 To: Stephan Szabo Cc: Josh Berkus; pgsql-performance@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxxx> To: <J@xxxxxxxxxxx> Cc: "Josh Berkus" <josh@xxxxxxxxxxxx>; <pgsql-performance@xxxxxxxxxxxxxx> Sent: Tuesday, January 17, 2006 6:39 PM Subject: Re: [PERFORM] Multiple Order By Criteria > > On Tue, 17 Jan 2006 J@xxxxxxxxxxx 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 >
