Re: joining two tables slow due to sequential scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 

 

-----Original Message-----
From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Tim Jones
Sent: Friday, February 10, 2006 4:07 PM
To: pgsql-performance@xxxxxxxxxxxxxx
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

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux