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';
= 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;
= 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