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 526DA9DC841 for ; Tue, 17 Jan 2006 19:03:19 -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 56643-08 for ; Tue, 17 Jan 2006 19:03:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from plain.rackshack.net (unknown [66.98.130.45]) by postgresql.org (Postfix) with ESMTP id 07ACB9DC81C for ; Tue, 17 Jan 2006 19:03:13 -0400 (AST) Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248]) by plain.rackshack.net (8.12.10/8.12.5) with SMTP id k0HMsN3x012824; Tue, 17 Jan 2006 17:54:25 -0500 From: J@Planeti.Biz Message-ID: <063d01c61bba$2d97fc00$681e140a@fatchubby> To: "Stephan Szabo" , "Josh Berkus" Cc: References: <05c301c61bb1$d88c6a00$681e140a@fatchubby> <200601171425.38772.josh@agliodbs.com> <20060117143952.D47156@megazone.bigpanda.com> Subject: Re: Multiple Order By Criteria Date: Tue, 17 Jan 2006 18:03:04 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.547 required=5 tests=[AWL=-0.172, BIZ_TLD=1.169, NO_REAL_NAME=0.55] X-Spam-Score: 1.547 X-Spam-Level: * X-Archive-Number: 200601/276 X-Sequence-Number: 16754 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 ? How do I make a reverse opclass ? ----- Original Message ----- From: "Stephan Szabo" To: "Josh Berkus" Cc: ; Sent: Tuesday, January 17, 2006 5:40 PM Subject: Re: [PERFORM] Multiple Order By Criteria > > On Tue, 17 Jan 2006, Josh Berkus wrote: > >> J, >> >> > I have an index built for each of these columns in my order by clause. >> > This query takes an unacceptable amount of time to execute. Here are >> > the >> > results of the explain: >> >> You need a single index which has all five columns, in order. > > I think he'll also need a reverse opclass for the first column in the > index or for the others since he's doing desc, asc, asc, asc, asc. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >