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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to