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 6E7E49DC800 for ; Fri, 10 Feb 2006 18:46:17 -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 26187-07 for ; Fri, 10 Feb 2006 18:46:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 50F0D9DC9E1 for ; Fri, 10 Feb 2006 18:46:13 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Fri, 10 Feb 2006 22:46:14 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 10 Feb 2006 16:46:14 -0600 Subject: Re: joining two tables slow due to sequential scan From: Scott Marlowe To: Tim Jones Cc: Dave Dutcher , pgsql-performance@postgresql.org In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D313131D@mail.optiosoftware.com> References: <47668A1334CDBF46927C1A0DFEB223D313131D@mail.optiosoftware.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1139611574.22740.154.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 10 Feb 2006 16:46:14 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.156 required=5 tests=[AWL=0.155, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.156 X-Spam-Level: X-Archive-Number: 200602/123 X-Sequence-Number: 17109 On Fri, 2006-02-10 at 16:43, Tim Jones wrote: > oops > > QUERY PLAN > 'Hash Join (cost=899.83..4384.17 rows=482 width=1350) (actual > time=0.203..0.203 rows=0 loops=1)' > ' Hash Cond: ("outer".documentidentifier = > "inner".dssdocumentidentifier)' > ' -> Seq Scan on documentversions (cost=0.00..2997.68 rows=96368 > width=996) (actual time=0.007..0.007 rows=1 loops=1)' > ' -> Hash (cost=898.62..898.62 rows=482 width=354) (actual > time=0.161..0.161 rows=0 loops=1)' > ' -> Bitmap Heap Scan on clinicaldocuments (cost=4.69..898.62 > rows=482 width=354) (actual time=0.159..0.159 rows=0 loops=1)' > ' Recheck Cond: (patientidentifier = 123)' > ' -> Bitmap Index Scan on ix_cdocpid (cost=0.00..4.69 > rows=482 width=0) (actual time=0.153..0.153 rows=0 loops=1)' > ' Index Cond: (patientidentifier = 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 Hmmmm. We really need to see what's happening on the real database to see what's going wrong. i.e. if the real database thinks it'll get 30 rows and it gets back 5,000,000 that's a problem. The query planner in pgsql is cost based, so until you have real data underneath it, and analyze it, you can't really say how it will behave for you. I.e. small test sets don't work.