From: | Hilary Forbes <hforbes(at)dmr(dot)co(dot)uk> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Slow update |
Date: | 2005-09-12 09:14:25 |
Message-ID: | 5.1.0.14.0.20050912100150.01f47f18@mailserver.dmr.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello everyone
I must be doing something very wrong here so help please! I have two tables
tableA has 300,000 recs
tableB has 20,000 recs
I need to set the value of a field in table A to a value in table B depending on the existence of the record in table B. So what I have done is
UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE a.key1=b.key1;
The primary key of tableA is key1 and that of tableB is key1 ie the join is on primary keys.
The "optimizer" has elected to d a sequential scan on tableA to determine which fields to update rather than the query being driveb by tableB and it is taking forever. Surely I must be able to force the system to read down tableB in preference to reading down tableA?
(Please don't ask why tableA and tableB are not amalgamated - that's another story altogether!!!)
Many thanks in advance
Hilary
Hilary Forbes
The DMR Information and Technology Group (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************
From | Date | Subject | |
---|---|---|---|
Next Message | Manfred Koizar | 2005-09-12 10:41:28 | Re: LEFT JOIN optimization |
Previous Message | John A Meinel | 2005-09-12 05:06:41 | Re: Advise about how to delete entries |