Skip site navigation (1) Skip section navigation (2)

Slow update

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 (view raw or flat)
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
**********************************************************


Responses

pgsql-performance by date

Next:From: Manfred KoizarDate: 2005-09-12 10:41:28
Subject: Re: LEFT JOIN optimization
Previous:From: John A MeinelDate: 2005-09-12 05:06:41
Subject: Re: Advise about how to delete entries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group