X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 54F6E9DC884 for ; Wed, 18 Jan 2006 01:51:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43128-02 for ; Wed, 18 Jan 2006 01:51:31 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id BA7B79DC833 for ; Wed, 18 Jan 2006 01:51:29 -0400 (AST) Received: from ki-communication.com (unknown [202.147.194.21]) by svr4.postgresql.org (Postfix) with ESMTP id 05CDA5AF07E for ; Wed, 18 Jan 2006 05:51:28 +0000 (GMT) Received: from edp01wks ([192.168.0.127]) by ki-communication.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 18 Jan 2006 12:54:53 +0700 From: "Ahmad Fajar" To: Subject: Re: Multiple Order By Criteria Date: Wed, 18 Jan 2006 12:54:44 +0700 Keywords: Postgresql Message-ID: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcYbxX5pEf+aCeStTbS7W2LJDBqN1QALCNdg In-Reply-To: <003201c61bc5$6652d690$81300d05@fatchubby> X-OriginalArrivalTime: 18 Jan 2006 05:54:53.0281 (UTC) FILETIME=[B3B7D510:01C61BF3] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.169 required=5 tests=[BIZ_TLD=1.169] X-Spam-Score: 1.169 X-Spam-Level: * X-Archive-Number: 200601/285 X-Sequence-Number: 16763 -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of J@Planeti.Biz Sent: Rabu, 18 Januari 2006 07:23 To: Stephan Szabo Cc: Josh Berkus; pgsql-performance@postgresql.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" To: Cc: "Josh Berkus" ; Sent: Tuesday, January 17, 2006 6:39 PM Subject: Re: [PERFORM] Multiple Order By Criteria > > On Tue, 17 Jan 2006 J@Planeti.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 >