JOIN not being calculated correctly
От | Scott Pederick |
---|---|
Тема | JOIN not being calculated correctly |
Дата | |
Msg-id | 001e01c4bc14$78cbda90$1d7af0dc@boblaptop обсуждение исходный текст |
Ответы |
Re: JOIN not being calculated correctly
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: