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
patientid integer (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.
select * from documentversions join clinicaldocuments on documentversions.documentidentifier
= clinicaldocuments.dssdocumentidentifier where documentversions.documentstatus = 'AC';
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