From: Bruno Wolff III <bruno@wolff.to>
To: Hilary Forbes <hforbes@dmr.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow update
Date: Mon, 12 Sep 2005 08:34:11 -0500
Message-ID: <20050912133411.GA21730@wolff.to> (raw)
In-Reply-To: <5.1.0.14.0.20050912100150.01f47f18@mailserver.dmr.co.uk>
References: <5.1.0.14.0.20050912100150.01f47f18@mailserver.dmr.co.uk>
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 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?
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruno@wolff.to, hforbes@dmr.co.uk
Subject: Re: Slow update
In-Reply-To: <20050912133411.GA21730@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox