From: Tim Jones <TJones@optio.com>
To: pgsql-performance@postgresql.org
Subject: joining two tables slow due to sequential scan
Date: Fri, 10 Feb 2006 17:06:35 -0500
Message-ID: <47668A1334CDBF46927C1A0DFEB223D3131302@mail.optiosoftware.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: TJones@optio.com
Subject: Re: joining two tables slow due to sequential scan
In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D3131302@mail.optiosoftware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox