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 4FB7A9DC800 for ; Fri, 10 Feb 2006 18:06:41 -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 20038-05 for ; Fri, 10 Feb 2006 18:06:42 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id E17A19DC855 for ; Fri, 10 Feb 2006 18:06:38 -0400 (AST) Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10]) by svr4.postgresql.org (Postfix) with ESMTP id 68CF75AF864 for ; Fri, 10 Feb 2006 22:06:41 +0000 (GMT) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C62E8E.437A3F6A" Subject: joining two tables slow due to sequential scan Date: Fri, 10 Feb 2006 17:06:35 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D3131302@mail.optiosoftware.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: joining two tables slow due to sequential scan Thread-Index: AcYujkHN1/mQ5kzZQ2ib5koRLF6qWA== From: "Tim Jones" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.081 required=5 tests=[AWL=0.080, HTML_MESSAGE=0.001] X-Spam-Score: 0.081 X-Spam-Level: X-Archive-Number: 200602/112 X-Sequence-Number: 17098 This is a multi-part message in MIME format. ------_=_NextPart_001_01C62E8E.437A3F6A Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable =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 ------_=_NextPart_001_01C62E8E.437A3F6A Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I am = trying to join=20 two tables and keep getting a sequential scan in the plan even though = there is=20 an index on the columns I am joining on. Basically this the = deal ...=20 I have two tables with docid in them which is what I am using for the=20 join.

ClinicalDocs ... (no=20 primary key) though it does not help if I make docid primary=20 key

docid = integer=20 (index)

patientid integer=20 (index)

visitid integer=20 (index)

...

Documentversions

docid = integer=20 (index)

docversionnumber=20 (index)

docversionidentifier=20 (primary key)

It = seems to do an=20 index scan if I put the primary key as docid. This is what occurs = when I=20 link on the patid from ClinicalDocs to patient table. However I = can not=20 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.

In = addition this=20 query

select = * from=20 documentversions join clinicaldocuments on=20 documentversions.documentidentifier
=3D = clinicaldocuments.dssdocumentidentifier=20 where documentversions.documentstatus =3D 'AC';

does = index scan=20

but if = I change the=20 order e.g

select * from = clinicaldocuments=20 join documentversions on clinicaldocuments.dssdocumentidentifier
=3D=20 documentversions .documentidentifier where = clinicaldocuments.patientidentifier=3D=20 123;

does sequential scan what I need is = bottom=20 query

it is extremely slow ... Any ideas=20 ?

Tim Jones

Healthcare Project = Manager

Optio Software,=20 Inc.

(770) = 576-3555

------_=_NextPart_001_01C62E8E.437A3F6A--