Inefficient query plan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

I have
work_mem = 10MB
shared_buffer = 256MB
effective_cache_size = 768MB

The database is basically for a single user.

Thanks a lot,
Jann


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux