Re: joining two tables slow due to sequential scan

for first query

QUERY PLAN
'Limit (cost=4.69..88.47 rows=10 width=1350) (actual
time=32.195..32.338 rows=10 loops=1)'
' -> Nested Loop (cost=4.69..4043.09 rows=482 width=1350) (actual
time=32.190..32.316 rows=10 loops=1)'
' -> Bitmap Heap Scan on documentversions (cost=4.69..1139.40
rows=482 width=996) (actual time=32.161..32.171 rows=10 loops=1)'
' Recheck Cond: (documentstatus = ''AC''::bpchar)'
' -> Bitmap Index Scan on ix_docstatus (cost=0.00..4.69
rows=482 width=0) (actual time=31.467..31.467 rows=96368 loops=1)'
' Index Cond: (documentstatus = ''AC''::bpchar)'
' -> Index Scan using ix_cdocdid on clinicaldocuments
(cost=0.00..6.01 rows=1 width=354) (actual time=0.006..0.007 rows=1
loops=10)'
' Index Cond: ("outer".documentidentifier =
clinicaldocuments.dssdocumentidentifier)'


for second query

QUERY PLAN
'Hash Join (cost=899.83..4384.17 rows=482 width=1350)'
' Hash Cond: ("outer".documentidentifier =
"inner".dssdocumentidentifier)'
' -> Seq Scan on documentversions (cost=0.00..2997.68 rows=96368
width=996)'
' -> Hash (cost=898.62..898.62 rows=482 width=354)'
' -> Bitmap Heap Scan on clinicaldocuments (cost=4.69..898.62
rows=482 width=354)'
' Recheck Cond: (patientidentifier = 123)'
' -> Bitmap Index Scan on ix_cdocpid (cost=0.00..4.69
rows=482 width=0)'
' Index Cond: (patientidentifier = 123)'

thnx

Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555

________________________________

From: Dave Dutcher [mailto:dave(at)tridecap(dot)com]
Sent: Friday, February 10, 2006 5:15 PM
To: Tim Jones; pgsql-performance(at)postgresql(dot)org
Subject: RE: [PERFORM] joining two tables slow due to sequential scan

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

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