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 C978D9DCD23 for ; Fri, 10 Feb 2006 18:37:35 -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 25580-03 for ; Fri, 10 Feb 2006 18:37:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10]) by postgresql.org (Postfix) with ESMTP id 126D79DCAA6 for ; Fri, 10 Feb 2006 18:37:30 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: joining two tables slow due to sequential scan Date: Fri, 10 Feb 2006 17:37:32 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D3131317@mail.optiosoftware.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] joining two tables slow due to sequential scan Thread-Index: AcYuj2fJETZfDwzXR6ibeZRXCfv5rQAAMd3g From: "Tim Jones" To: "Dave Dutcher" , X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.103 required=5 tests=[AWL=0.103] X-Spam-Score: 0.103 X-Spam-Level: X-Archive-Number: 200602/117 X-Sequence-Number: 17103 for first query QUERY PLAN 'Limit (cost=3D4.69..88.47 rows=3D10 width=3D1350) (actual time=3D32.195..32.338 rows=3D10 loops=3D1)' ' -> Nested Loop (cost=3D4.69..4043.09 rows=3D482 width=3D1350) = (actual time=3D32.190..32.316 rows=3D10 loops=3D1)' ' -> Bitmap Heap Scan on documentversions (cost=3D4.69..1139.40 rows=3D482 width=3D996) (actual time=3D32.161..32.171 rows=3D10 = loops=3D1)' ' Recheck Cond: (documentstatus =3D ''AC''::bpchar)' ' -> Bitmap Index Scan on ix_docstatus (cost=3D0.00..4.69 rows=3D482 width=3D0) (actual time=3D31.467..31.467 rows=3D96368 = loops=3D1)' ' Index Cond: (documentstatus =3D ''AC''::bpchar)' ' -> Index Scan using ix_cdocdid on clinicaldocuments (cost=3D0.00..6.01 rows=3D1 width=3D354) (actual time=3D0.006..0.007 = rows=3D1 loops=3D10)' ' Index Cond: ("outer".documentidentifier =3D clinicaldocuments.dssdocumentidentifier)' =20 =20 for second query QUERY PLAN 'Hash Join (cost=3D899.83..4384.17 rows=3D482 width=3D1350)' ' Hash Cond: ("outer".documentidentifier =3D "inner".dssdocumentidentifier)' ' -> Seq Scan on documentversions (cost=3D0.00..2997.68 rows=3D96368 width=3D996)' ' -> Hash (cost=3D898.62..898.62 rows=3D482 width=3D354)' ' -> Bitmap Heap Scan on clinicaldocuments (cost=3D4.69..898.62 rows=3D482 width=3D354)' ' Recheck Cond: (patientidentifier =3D 123)' ' -> Bitmap Index Scan on ix_cdocpid (cost=3D0.00..4.69 rows=3D482 width=3D0)' ' Index Cond: (patientidentifier =3D 123)' thnx Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 =20 ________________________________ From: Dave Dutcher [mailto:dave@tridecap.com]=20 Sent: Friday, February 10, 2006 5:15 PM To: Tim Jones; pgsql-performance@postgresql.org Subject: RE: [PERFORM] joining two tables slow due to sequential scan What version of postgres are you using? Can you post the output from EXPLAIN ANALYZE? =20 =20 -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tim Jones Sent: Friday, February 10, 2006 4:07 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] joining two tables slow due to sequential scan =20 =20 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. =20 =20 ClinicalDocs ... (no primary key) though it does not help if I make docid primary key docid integer (index) patientid integer (index) visitid integer (index) ... =20 Documentversions docid integer (index) docversionnumber (index) docversionidentifier (primary key) =20 It seems to do an index scan if I put the primary key as docid. This is what occurs when I link on the patid from ClinicalDocs to patient table. However I can not make the docid primary key because it gets repeated depending on how may versions of a document I have. I have tried using a foreign key on documentversions with no sucess.=20 =20 In addition this query =20 select * from documentversions join clinicaldocuments on documentversions.documentidentifier =3D clinicaldocuments.dssdocumentidentifier where documentversions.documentstatus =3D 'AC';=20 =20 does index scan=20 but if I change the order e.g =20 select * from clinicaldocuments join documentversions on clinicaldocuments.dssdocumentidentifier =3D documentversions .documentidentifier where clinicaldocuments.patientidentifier=3D 123; =20 does sequential scan what I need is bottom query it is extremely slow ... Any ideas ? =20 Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 =20