From: Scott Pederick <scott@pederick.com>
To: pgsql-sql@postgresql.org
Subject: JOIN not being calculated correctly
Date: Wed, 27 Oct 2004 21:02:35 +1000
Message-ID: <001e01c4bc14$78cbda90$1d7af0dc@boblaptop> (raw)
Hi all!
I'm having a problem with Postgresql 8.0.0-beta4 on a Win2K machine and a
particular join.
I've got two tables - a list of customers and jobs they've had. A customer
can have multiple jobs.
The query always scans the entire jobs table for each customer - I need it
the other way around so I can get a list of the customers who have at least
one job.
The EXPLAIN shows the jobs table is being scanned for some reason:
Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Customers
INNER JOIN Jobs USING (CustomerId);
QUERY PLAN
-------------------------------------------------------------------------
Hash Join (cost=78.54..4908.71 rows=70727 width=8)
Hash Cond: ("outer".customerid = "inner".customerid)
-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
-> Hash (cost=76.03..76.03 rows=1003 width=4)
-> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
(5 rows)
Even if I reverse the JOIN I get the exact same result:
Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Jobs INNER
JOIN Customers USING (CustomerId);
QUERY PLAN
-------------------------------------------------------------------------
Hash Join (cost=78.54..4908.71 rows=70727 width=8)
Hash Cond: ("outer".customerid = "inner".customerid)
-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
-> Hash (cost=76.03..76.03 rows=1003 width=4)
-> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
(5 rows)
How can I force it to operate as I need it to? It seems the query engine is
a little smarter than it needs to be.
If anyone can shed some light on this problem, it would be greatly
appreciated. I've taken it as far as I can and don't really know where to
move from here.
Thanks in advance,
Scott Pederick
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scott@pederick.com
Subject: Re: JOIN not being calculated correctly
In-Reply-To: <001e01c4bc14$78cbda90$1d7af0dc@boblaptop>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox