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: Mon, 13 Feb 2006 11:21:58 -0500
Message-ID: <47668A1334CDBF46927C1A0DFEB223D3131457@mail.optiosoftware.com>
Thread-Topic: [PERFORM] joining two tables slow due to sequential scan 
Thread-Index: AcYumUTCGLv3XAabSGacxcKb3ISIkACIB5zw
From: "Tim Jones" <TJones@optio.com>
To: "Dave Dutcher" <dave@tridecap.com>
Cc: <pgsql-performance@postgresql.org>

ok I am retarded :) Apparently I thought I had done analyze on these
tables but I actually had not and that was all that was needed. but
thanks for the help.


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

-----Original Message-----
From: Dave Dutcher [mailto:dave@tridecap.com]=20
Sent: Friday, February 10, 2006 6:25 PM
To: Tim Jones
Cc: pgsql-performance@postgresql.org
Subject: RE: [PERFORM] joining two tables slow due to sequential scan=20

OK, if I'm reading this correctly, it looks like the planner is choosing
a sequential scan because it expects 48,000 rows for that
patientidentifier, but its actually only getting 3.  The planner has the
number of rows right for the sequential scan, so it seems like the stats
are up to date.  I would try increasing the stats for the
patientindentifier column with 'alter table set statistics...' or
increasing the default_statistics_target for the whole DB.  Once you
have changed the stats I believe you need to run analyze again.


-----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:59 PM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan=20

ok here is real db

the first  query I had seems to make no sense because it is only fast if
I limit the rows since almost all rows have status =3D 'AC'

second query
 tables both have about 10 million rows and it takes a long time as you
can see but this person only has approx 160 total documents


 QUERY PLAN     =20
------------------------------------------------------------------------
-------------------------------------------------------------------
 Hash Join  (cost=3D84813.14..1510711.97 rows=3D48387 width=3D555) =
(actual
time=3D83266.854..91166.315 rows=3D3 loops=3D1)
   Hash Cond: ("outer".documentidentifier =3D
"inner".dssdocumentidentifier)
   ->  Seq Scan on documentversions  (cost=3D0.00..269141.98 =
rows=3D9677398
width=3D415) (actual time=3D0.056..49812.459 rows=3D9677398 loops=3D1)
   ->  Hash  (cost=3D83660.05..83660.05 rows=3D48036 width=3D140) =
(actual
time=3D10.833..10.833 rows=3D3 loops=3D1)
         ->  Bitmap Heap Scan on clinicaldocuments
(cost=3D301.13..83660.05 rows=3D48036 width=3D140) (actual =
time=3D0.243..0.258
rows=3D3 loops=3D1)
               Recheck Cond: (patientidentifier =3D 690193)
               ->  Bitmap Index Scan on ix_cdocpid  (cost=3D0.00..301.13
rows=3D48036 width=3D0) (actual time=3D0.201..0.201 rows=3D3 loops=3D1)
                     Index Cond: (patientidentifier =3D 690193)  Total
runtime: 91166.540 ms


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

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, February 10, 2006 5:52 PM
To: Tim Jones
Cc: Scott Marlowe; Dave Dutcher; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan=20

"Tim Jones" <TJones@optio.com> writes:
> QUERY PLAN
> 'Hash Join  (cost=3D899.83..4384.17 rows=3D482 width=3D1350) (actual
> time=3D0.203..0.203 rows=3D0 loops=3D1)'
> ...
> 'Total runtime: 0.392 ms'

Hardly seems like evidence of a performance problem ...

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings