To: pgsql-sql@postgresql.org
From: =?UTF-8?B?SmFubiBSw7ZkZXI=?= <roederja@ethz.ch>
Subject: Re: Inefficient query plan
Date: Mon, 23 Aug 2010 12:58:06 +0200
Lines: 43
Message-ID: <i4tk7u$pmu$1@dough.gmane.org>
References: <i4ss30$bq1$1@dough.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; de;
	rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
In-Reply-To: <i4ss30$bq1$1@dough.gmane.org>

Please ignore this post. I accidentally double posted here and on the
performance mailing list. I think this belongs on the performance list.

Sorry for the noise.
Jann

Am 23.08.10 06:05, schrieb Jann Röder:
> I have two tables:
> A: ItemID (PK), IsssueID (Indexed)
> B: ItemID (FK), IndexNumber : PK(ItemID, IndexNumber)
> 
> Both tables have several million columns, but B has much more than A.
> 
> Now if I run
> 
> SELECT A.ItemID FROM A, B WHERE A.ItemID = B.itemID AND A.issueID =
> <some id>
> 
> The query takes extremely long (several hours). I ran EXPLAIN and got:
> 
> "Hash Join  (cost=516.66..17710110.47 rows=8358225 width=16)"
> "  Hash Cond: ((b.itemid)::bpchar = a.itemid)"
> "  ->  Seq Scan on b  (cost=0.00..15110856.68 rows=670707968 width=16)"
> "  ->  Hash  (cost=504.12..504.12 rows=1003 width=16)"
> "        ->  Index Scan using idx_issueid on a  (cost=0.00..504.12
> rows=1003 width=16)"
> "              Index Cond: (issueid = 'A1983PW823'::bpchar)"
> 
> Now we see the problem is the seq scan on B. However there are only a
> handful of rows in A that match a specific issueID. So my question is
> why doesn't it just check for each of the ItemIDs that have the correct
> IssueID in A if there is a matching itemID in B. This should be really
> fast because ItemID in B is indexed since it is part of the primary key.
> 
> What is the reason for postgres not doing this, is there a way I can
> make it do that? I'm using postgresql 8.4.4 and yes, I did run ANALYZE
> on the entire DB.
> 
> Thanks a lot,
> Jann
> 
>