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 DBA299DC95C for ; Fri, 10 Feb 2006 18:14:47 -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 23461-01 for ; Fri, 10 Feb 2006 18:14:49 -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 942A69DC855 for ; Fri, 10 Feb 2006 18:14:42 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [63.226.156.118] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 83442275; Fri, 10 Feb 2006 14:14:40 -0800 From: "Dave Dutcher" To: "'Tim Jones'" , Subject: Re: joining two tables slow due to sequential scan Date: Fri, 10 Feb 2006 16:14:30 -0600 Message-ID: <001f01c62e8f$63551840$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0020_01C62E5D.18BAA840" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D3131302@mail.optiosoftware.com> Importance: Normal 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/113 X-Sequence-Number: 17099 This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C62E5D.18BAA840 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit What version of postgres are you using? Can you post the output from EXPLAIN ANALYZE? -----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 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. ClinicalDocs ... (no primary key) though it does not help if I make docid primary key docid integer (index) patientid integer (index) visitid integer (index) ... Documentversions docid integer (index) docversionnumber (index) docversionidentifier (primary key) 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. In addition this query 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; does sequential scan what I need is bottom query it is extremely slow ... Any ideas ? Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 ------=_NextPart_000_0020_01C62E5D.18BAA840 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

What version of postgres are you using? Can you = post the output from EXPLAIN ANALYZE?

-----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

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. =

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

docid integer = (index)

patientid integer = (index)

visitid integer = (index)

...=

Documentversions

docid integer = (index)

docversionnumber = (index)

docversionidentifier = (primary key)

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.

In addition this = query

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

does index scan =

but if I change the order = e.g

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

does sequential = scan what I need is bottom query

it is extremely slow ... = Any ideas ?

Tim = Jones

Healthcare Project = Manager

Optio Software, = Inc.

(770) = 576-3555

------=_NextPart_000_0020_01C62E5D.18BAA840--