Skip site navigation (1) Skip section navigation (2)

Re: Inefficient query plan

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Jann Röder <roederja(at)ethz(dot)ch>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Inefficient query plan
Date: 2010-08-23 05:51:22
Message-ID: AANLkTi=JmkJ-HuXUKfNW969c+jYuFtHoH-zk0j8y-k0C@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Aug 22, 2010 at 10:23 PM, Jann Röder <roederja(at)ethz(dot)ch> wrote:
> 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)"

Have you tried adding an index on b.indexid?

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-08-23 05:52:50
Subject: Re: Inefficient query plan
Previous:From: Jann RöderDate: 2010-08-23 04:23:38
Subject: Inefficient query plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group