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 2FAA19DC826 for ; Fri, 10 Feb 2006 18:22:09 -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 21565-08 for ; Fri, 10 Feb 2006 18:22:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id D237B9DC800 for ; Fri, 10 Feb 2006 18:22:06 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Fri, 10 Feb 2006 22:22:05 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 10 Feb 2006 16:22:05 -0600 Subject: Re: joining two tables slow due to sequential scan From: Scott Marlowe To: Tim Jones Cc: pgsql-performance@postgresql.org In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D3131302@mail.optiosoftware.com> References: <47668A1334CDBF46927C1A0DFEB223D3131302@mail.optiosoftware.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1139610125.22740.145.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 10 Feb 2006 16:22:05 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.158 required=5 tests=[AWL=0.157, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.158 X-Spam-Level: X-Archive-Number: 200602/114 X-Sequence-Number: 17100 On Fri, 2006-02-10 at 16:06, Tim Jones wrote: > > I am trying to join two tables and keep getting a sequential scan in > the plan even though there is an index on the columns I am joining > on. Basically this the deal ... I have two tables with docid in them > which is what I am using for the join. > SNIP > select * from documentversions join clinicaldocuments on > documentversions.documentidentifier > = clinicaldocuments.dssdocumentidentifier where > documentversions.documentstatus = 'AC'; > > does index scan > but if I change the order e.g > > select * from clinicaldocuments join documentversions on > clinicaldocuments.dssdocumentidentifier > = documentversions .documentidentifier where > clinicaldocuments.patientidentifier= 123; OK. I'm gonna make a couple of guesses here: 1: clinicaldocuments.patientidentifier is an int8 and you're running 7.4 or before. 2: There are more rows with clinicaldocuments.patientidentifier= 123 than with documentversions.documentstatus = 'AC'. 3: documentversions.documentidentifier and clinicaldocuments.dssdocumentidentifier are not the same type. Any of those things true?