Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow update

Slow update

From
Hilary Forbes
Date:
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
beingdriveb by tableB and it is taking forever.  Surely I must be able to force the system to read down tableB in
preferenceto 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
**********************************************************

Re: Slow update

From
Richard Huxton
Date:
Hilary Forbes wrote:
>
> 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;

Check the EXPLAIN carefully, are you sure the tableA in "UPDATE" is the
same as that in your "FROM" clause. If so, why are you SETting a.val1?

If not, you've probably got an unconstrained join.
--
   Richard Huxton
   Archonet Ltd

Re: Slow update

From
Bruno Wolff III
Date:
On Mon, Sep 12, 2005 at 10:14:25 +0100,
  Hilary Forbes <hforbes@dmr.co.uk> wrote:
> 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
querybeing driveb by tableB and it is taking forever.  Surely I must be able to force the system to read down tableB in
preferenceto reading down tableA? 

It would help to see the exact query and the explain analyze output. Hopefully
you didn't really write the query similar to above, since it is using illegal
syntax and the if it was changed slightly to become legal than it would do a
cross join of table A with the inner join of tableA and tableB, which isn't
what you want.

Re: Slow update

From
Tom Lane
Date:
Hilary Forbes <hforbes@dmr.co.uk> writes:
> 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;

You've written an unconstrained join to a second copy of tableA.

            regards, tom lane