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 4E4239DC826
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 18:39:21 -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 25354-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 18:39:23 -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 78BB49DC800
	for <pgsql-performance@postgresql.org>;
	Fri, 10 Feb 2006 18:39:18 -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:39:19 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	10 Feb 2006 16:39:19 -0600
Subject: Re: joining two tables slow due to sequential scan
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Tim Jones <TJones@optio.com>
Cc: Dave Dutcher <dave@tridecap.com>, pgsql-performance@postgresql.org
In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D3131317@mail.optiosoftware.com>
References: <47668A1334CDBF46927C1A0DFEB223D3131317@mail.optiosoftware.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1139611159.22740.149.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:39:19 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.157 required=5 tests=[AWL=0.156,
	UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 0.157
X-Spam-Level: 
X-Archive-Number: 200602/119
X-Sequence-Number: 17105

On Fri, 2006-02-10 at 16:37, Tim Jones wrote:
> for first query
> 
> QUERY PLAN
> 'Limit  (cost=4.69..88.47 rows=10 width=1350) (actual
> time=32.195..32.338 rows=10 loops=1)'
> '  ->  Nested Loop  (cost=4.69..4043.09 rows=482 width=1350) (actual
> time=32.190..32.316 rows=10 loops=1)'
> '        ->  Bitmap Heap Scan on documentversions  (cost=4.69..1139.40
> rows=482 width=996) (actual time=32.161..32.171 rows=10 loops=1)'
> '              Recheck Cond: (documentstatus = ''AC''::bpchar)'
> '              ->  Bitmap Index Scan on ix_docstatus  (cost=0.00..4.69
> rows=482 width=0) (actual time=31.467..31.467 rows=96368 loops=1)'
> '                    Index Cond: (documentstatus = ''AC''::bpchar)'
> '        ->  Index Scan using ix_cdocdid on clinicaldocuments
> (cost=0.00..6.01 rows=1 width=354) (actual time=0.006..0.007 rows=1
> loops=10)'
> '              Index Cond: ("outer".documentidentifier =
> clinicaldocuments.dssdocumentidentifier)'
>  
> 
>  for second query
> 
> QUERY PLAN
> 'Hash Join  (cost=899.83..4384.17 rows=482 width=1350)'
> '  Hash Cond: ("outer".documentidentifier =
> "inner".dssdocumentidentifier)'
> '  ->  Seq Scan on documentversions  (cost=0.00..2997.68 rows=96368
> width=996)'
> '  ->  Hash  (cost=898.62..898.62 rows=482 width=354)'
> '        ->  Bitmap Heap Scan on clinicaldocuments  (cost=4.69..898.62
> rows=482 width=354)'
> '              Recheck Cond: (patientidentifier = 123)'
> '              ->  Bitmap Index Scan on ix_cdocpid  (cost=0.00..4.69
> rows=482 width=0)'
> '                    Index Cond: (patientidentifier = 123)'

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