X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 556929DCD92 for ; Fri, 10 Feb 2006 18:59:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28617-05 for ; Fri, 10 Feb 2006 18:59:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10]) by postgresql.org (Postfix) with ESMTP id E9D369DCD86 for ; Fri, 10 Feb 2006 18:59:02 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 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:59:03 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D3131324@mail.optiosoftware.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] joining two tables slow due to sequential scan Thread-Index: AcYulJakTYfEeSl+RnqOOKcAiwGtCgAABsnw From: "Tim Jones" To: "Tom Lane" Cc: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109] X-Spam-Score: 0.109 X-Spam-Level: X-Archive-Number: 200602/125 X-Sequence-Number: 17111 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]=20 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" 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