Re: JOIN not being calculated correctly - Mailing list pgsql-sql

Hi Andrew,

Thanks for your response - sorry it's taken so long to reply, I've been out
of contact for a while.

Still can't fathom why it's not using an index scan on the jobs table..

: Posting the EXPLAIN is a good, an EXPLAIN ANALYZE would be better
: (assuming your dataset is small enough for it to complete in this
: lifetime). You also need to include the following information:
:
: 1) The schema involved, including information about indexes being used.

Here's the EXPLAIN ANALYZE, schemas and indexes:

Quarry=> EXPLAIN ANALYZE SELECT Customers.CustomerId, Jobs.JobId FROM
Customers INNER JOIN Jobs USING (CustomerId);                                                     QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------Hash Join  (cost=78.54..7924.71 rows=70727 width=8) (actual
time=50.000..21941.000 rows=70727 loops=1)  Hash Cond: ("outer".customerid = "inner".customerid)  ->  Seq Scan on jobs
(cost=0.00..6785.27rows=70727 width=8) (actual
 
time=40.000..21040.000 rows=70727 loops=1)  ->  Hash  (cost=76.03..76.03 rows=1003 width=4) (actual
time=10.000..10.000 rows=0 loops=1)        ->  Seq Scan on customers  (cost=0.00..76.03 rows=1003 width=4)
(actual time=0.000..0.000 rows=1003 loops=1)Total runtime: 22292.000 ms
(6 rows)


Customers
---
CREATE TABLE customers (   customerid serial NOT NULL,   account character varying(6) DEFAULT ''::character varying NOT
NULL,  businessname character varying(40) DEFAULT ''::character varying NOT
 
NULL,   address character varying(30) DEFAULT ''::character varying NOT NULL,   suburb character varying(30) DEFAULT
''::charactervarying NOT NULL,   postcode character varying(4) DEFAULT ''::character varying NOT NULL,   state
character(3)DEFAULT ''::bpchar NOT NULL,   mobile character varying(14) DEFAULT ''::character varying NOT NULL,   fax
charactervarying(14) DEFAULT ''::character varying NOT NULL,   acctitle character varying(4) DEFAULT ''::character
varyingNOT NULL,   accfirst character varying(14) DEFAULT ''::character varying NOT NULL,   acclast character
varying(14)DEFAULT ''::character varying NOT NULL,   accphone character varying(14) DEFAULT ''::character varying NOT
NULL,  accfax character(1) DEFAULT ''::bpchar NOT NULL,   accmobile character varying(14) DEFAULT ''::character varying
NOTNULL,   alttitle character varying(4) DEFAULT ''::character varying NOT NULL,   altfirst character varying(14)
DEFAULT''::character varying NOT NULL,   altlast character varying(14) DEFAULT ''::character varying NOT NULL,
currentnumeric(10,2) DEFAULT 0 NOT NULL,   aged30 numeric(10,2) DEFAULT 0 NOT NULL,   aged60 numeric(10,2) DEFAULT 0
NOTNULL,   aged90 numeric(10,2) DEFAULT 0 NOT NULL,   ytd numeric(10,2) DEFAULT 0 NOT NULL,   pg character(1) DEFAULT
''::bpcharNOT NULL,   acclimit character varying(14) DEFAULT ''::character varying NOT NULL,   accauto character(1)
DEFAULT''::bpchar NOT NULL,   text text DEFAULT ''::text NOT NULL,   fee character(1) DEFAULT ''::bpchar NOT NULL,
gstcharacter(1) DEFAULT ''::bpchar NOT NULL,   stop character(1) DEFAULT ''::bpchar NOT NULL
 
);

Customers - Indexes
---
customers_pkey PRIMARY KEY (customerid);
UNIQUE INDEX customers_account ON customers USING btree (account);



Jobs
---
CREATE TABLE jobs (   jobid serial NOT NULL,   customerid integer,   quarryid integer,   producttypeid integer,
invoiceidinteger,   salesid integer,   orderid character varying(15) DEFAULT ''::character varying NOT NULL,   jobdate
date,  contactname character varying(40) DEFAULT ''::character varying NOT
 
NULL,   businessname character varying(40) DEFAULT ''::character varying NOT
NULL,   address character varying(30) DEFAULT ''::character varying NOT NULL,   suburb character varying(30) DEFAULT
''::charactervarying NOT NULL,   phone character varying(14) DEFAULT ''::character varying NOT NULL,   melway character
varying(4)DEFAULT ''::character varying NOT NULL,   melxy character(3) DEFAULT ''::bpchar NOT NULL,   quarryorder
numeric(10,2)DEFAULT 0 NOT NULL,   quarrypickup numeric(10,2) DEFAULT 0 NOT NULL,   quarryprice numeric(10,2) DEFAULT 0
NOTNULL,   transport numeric(10,2) DEFAULT 0 NOT NULL,   margin numeric(10,2) DEFAULT 0 NOT NULL,   unit character(1)
DEFAULT''::bpchar NOT NULL,   done character(1) DEFAULT ''::bpchar NOT NULL,   xno character varying(10) DEFAULT
''::charactervarying NOT NULL,   mzone character(1) DEFAULT ''::bpchar NOT NULL,   invoice numeric(10,2) DEFAULT 0 NOT
NULL,  drivercomment character varying(12) DEFAULT ''::character varying NOT
 
NULL   text text DEFAULT ''::text NOT NULL,   gst numeric(10,2) DEFAULT 0 NOT NULL,   distance numeric(7,2) DEFAULT 0
NOTNULL,   productdefinitionid integer,   customerprice numeric(10,2) DEFAULT 0 NOT NULL
 
);

Jobs Keys/Indexes
---
jobs_pkey PRIMARY KEY (jobid);
INDEX jobs_customerid ON jobs USING btree (customerid);
INDEX jobs_jobdate ON jobs USING btree (jobdate);
INDEX jobs_quarryid ON jobs USING btree (quarryid);

CONSTRAINT jobs_customerid_fkey FOREIGN KEY (customerid) REFERENCES
customers(customerid) ON UPDATE RESTRICT ON DELETE RESTRICT;
CONSTRAINT jobs_invoiceid_fkey FOREIGN KEY (invoiceid) REFERENCES
invoices(invoiceid) ON UPDATE RESTRICT ON DELETE RESTRICT;
ADD CONSTRAINT jobs_productdefinitionid_fkey FOREIGN KEY
(productdefinitionid) REFERENCES producttypes(producttypeid) ON UPDATE
RESTRICT;
CONSTRAINT jobs_producttypeid_fkey FOREIGN KEY (producttypeid) REFERENCES
producttypes(producttypeid) ON UPDATE RESTRICT ON DELETE RESTRICT;
CONSTRAINT jobs_quarryid_fkey FOREIGN KEY (quarryid) REFERENCES
quarries(quarryid) ON UPDATE RESTRICT ON DELETE RESTRICT;
CONSTRAINT jobs_salesid_fkey FOREIGN KEY (salesid) REFERENCES sales(salesid)
ON UPDATE RESTRICT ON DELETE RESTRICT;



: 2) Have you vacuumed / analyzed the tables involved recently?

Yes, I ran "VACUUM ANALYZE" on both table before I started.


: 3) Have you modified the stats on any of the tables / columns involve or
: are you using defaults?

I'm using the defaults...

Thanks in advance to anyone who has taken the time to wade through this
one... :)

Scott

:
: Drew
:
:
: Scott Pederick wrote:
: | 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.
:
:
: - --
: Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
: Database Administrator, Afilias Canada Corp.
: CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
: -----BEGIN PGP SIGNATURE-----
: Version: GnuPG v1.2.5 (GNU/Linux)
:
: iD8DBQFBh9YQgfzn5SevSpoRAg0LAKCg5K7IccFIOvdTc8DEl2YaUMcUCgCfRt2Q
: CI1Vo6yxHkrWcoTQMQ/EvOw=
: =m15B
: -----END PGP SIGNATURE-----
:
: ---------------------------(end of broadcast)---------------------------
: TIP 3: if posting/reading through Usenet, please send an appropriate
:       subscribe-nomail command to majordomo@postgresql.org so that your
:       message can get through to the mailing list cleanly
: