Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: joining two tables slow due to sequential scan
Date: Fri, 10 Feb 2006 17:43:58 -0500
Message-ID: <47668A1334CDBF46927C1A0DFEB223D313131D@mail.optiosoftware.com>
Thread-Topic: [PERFORM] joining two tables slow due to sequential scan
Thread-Index: AcYuktoSqNJAD8cZSKOqTVIO5IZaoAAAE2Hw
From: "Tim Jones" <TJones@optio.com>
To: "Scott Marlowe" <smarlowe@g2switchworks.com>
Cc: "Dave Dutcher" <dave@tridecap.com>,
	<pgsql-performance@postgresql.org>

oops

QUERY PLAN
'Hash Join  (cost=3D899.83..4384.17 rows=3D482 width=3D1350) (actual
time=3D0.203..0.203 rows=3D0 loops=3D1)'
'  Hash Cond: ("outer".documentidentifier =3D
"inner".dssdocumentidentifier)'
'  ->  Seq Scan on documentversions  (cost=3D0.00..2997.68 rows=3D96368
width=3D996) (actual time=3D0.007..0.007 rows=3D1 loops=3D1)'
'  ->  Hash  (cost=3D898.62..898.62 rows=3D482 width=3D354) (actual
time=3D0.161..0.161 rows=3D0 loops=3D1)'
'        ->  Bitmap Heap Scan on clinicaldocuments  (cost=3D4.69..898.62
rows=3D482 width=3D354) (actual time=3D0.159..0.159 rows=3D0 loops=3D1)'
'              Recheck Cond: (patientidentifier =3D 123)'
'              ->  Bitmap Index Scan on ix_cdocpid  (cost=3D0.00..4.69
rows=3D482 width=3D0) (actual time=3D0.153..0.153 rows=3D0 loops=3D1)'
'                    Index Cond: (patientidentifier =3D 123)'
'Total runtime: 0.392 ms'

note I have done these on a smaller db than what I am using but the
plans are the same=20


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

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]=20
Sent: Friday, February 10, 2006 5:39 PM
To: Tim Jones
Cc: Dave Dutcher; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan

On Fri, 2006-02-10 at 16:37, Tim Jones wrote:
> for first query
>=20
> QUERY PLAN
> 'Limit  (cost=3D4.69..88.47 rows=3D10 width=3D1350) (actual
> time=3D32.195..32.338 rows=3D10 loops=3D1)'
> '  ->  Nested Loop  (cost=3D4.69..4043.09 rows=3D482 width=3D1350) =
(actual
> time=3D32.190..32.316 rows=3D10 loops=3D1)'
> '        ->  Bitmap Heap Scan on documentversions  =
(cost=3D4.69..1139.40
> rows=3D482 width=3D996) (actual time=3D32.161..32.171 rows=3D10 =
loops=3D1)'
> '              Recheck Cond: (documentstatus =3D ''AC''::bpchar)'
> '              ->  Bitmap Index Scan on ix_docstatus  =
(cost=3D0.00..4.69
> rows=3D482 width=3D0) (actual time=3D31.467..31.467 rows=3D96368 =
loops=3D1)'
> '                    Index Cond: (documentstatus =3D ''AC''::bpchar)'
> '        ->  Index Scan using ix_cdocdid on clinicaldocuments
> (cost=3D0.00..6.01 rows=3D1 width=3D354) (actual time=3D0.006..0.007 =
rows=3D1=20
> loops=3D10)'
> '              Index Cond: ("outer".documentidentifier =3D
> clinicaldocuments.dssdocumentidentifier)'
> =20
>=20
>  for second query
>=20
> QUERY PLAN
> 'Hash Join  (cost=3D899.83..4384.17 rows=3D482 width=3D1350)'
> '  Hash Cond: ("outer".documentidentifier =3D=20
> "inner".dssdocumentidentifier)'
> '  ->  Seq Scan on documentversions  (cost=3D0.00..2997.68 =
rows=3D96368=20
> width=3D996)'
> '  ->  Hash  (cost=3D898.62..898.62 rows=3D482 width=3D354)'
> '        ->  Bitmap Heap Scan on clinicaldocuments  =
(cost=3D4.69..898.62
> rows=3D482 width=3D354)'
> '              Recheck Cond: (patientidentifier =3D 123)'
> '              ->  Bitmap Index Scan on ix_cdocpid  (cost=3D0.00..4.69
> rows=3D482 width=3D0)'
> '                    Index Cond: (patientidentifier =3D 123)'

OK, the first one is explain analyze, but the second one is just plain
explain...