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 92D6C9DCBB2 for ; Tue, 28 Mar 2006 13:20:26 -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 12567-10 for ; Tue, 28 Mar 2006 13:20:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id DADE49DCA33 for ; Tue, 28 Mar 2006 13:20:20 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [216.160.41.114] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 86698039; Tue, 28 Mar 2006 09:20:19 -0800 From: "Dave Dutcher" To: "'Steinar H. Gunderson'" , Subject: Re: simple join uses indexes, very slow Date: Tue, 28 Mar 2006 11:20:19 -0600 Message-ID: <00da01c6528b$e3b6fd30$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 In-Reply-To: <20060328162908.GB26539@uio.no> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200603/547 X-Sequence-Number: 17984 > -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] On Behalf Of Steinar H. Gunderson > Sent: Tuesday, March 28, 2006 10:29 AM > > An index on (A,B,C) can be used for a query on (A,B) or (A), so it doesn't > really matter. It isn't usable for a query on (B), (C) or (B,C), though. > (The > index rows will get bigger, of course, so you'll need more I/O if you want > to > scan large parts of it, but I guess that's beside the point.) I guess what I am really curious about is why was the OP getting an expensive sort when the planner tried a merge join? Most of the time was spent sorting the parameters parameters table by opset_num even though opset_num is indexed. Isn't Postgres able to walk the index instead of sorting? I was wondering if maybe Postgres wasn't recognizing that it could just walk the index because the opset_num column isn't the first in the index.