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 NOT NULL,
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 Hammond416-673-4138[EMAIL PROTECTED]
: 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 [EMAIL PROTECTED] so that your
: message can get through to the mailing list cleanly
:
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
