Message-ID: <42F4BC61.20102@arbash-meinel.com>
Date: Sat, 06 Aug 2005 08:34:25 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
MIME-Version: 1.0
To: Patrick Hatcher <pathat@comcast.net>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow update statement
References: <42F4B812.7070501@comcast.net>
In-Reply-To: <42F4B812.7070501@comcast.net>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigB102F44065A4841D42023663"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigB102F44065A4841D42023663
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Patrick Hatcher wrote:
> [Reposted from General section with updated information]
> Pg 7.4.5
>
> I'm running an update statement on about 12 million records using the
> following query:
>
> Update table_A
> set F1 = b.new_data
> from table_B b
> where b.keyfield = table_A.keyfield
>
> both keyfields are indexed, all other keys in table_A were dropped, yet
> this job has been running over 15 hours.  Is
> this normal?

Can you do an EXPLAIN UPDATE so that we can have an idea what the
planner is trying to do?

My personal concern is if it doing something like pulling in all rows
from b, and then one by one updating table_A, but as it is going, it
can't retire any dead rows, because you are still in a transaction. So
you are getting a lot of old rows, which it has to pull in to realize it
was old.

How many rows are in table_B?

I can see that possibly doing it in smaller chunks might be faster, as
would inserting into another table. But I would do more of a test and
see what happens.

John
=:->

>
> I stopped the process the first time after 3 hours of running due to
> excessive log rotation and reset the conf file to these settings:
>
>
> wal_buffers = 64                # min 4, 8KB each
>
> # - Checkpoints -
>
> checkpoint_segments = 128       # in logfile segments, min 1, 16MB each
> checkpoint_timeout = 1800       # range 30-3600, in seconds
> #checkpoint_warning = 30        # 0 is off, in seconds
> #commit_delay = 0               # range 0-100000, in microseconds
> #commit_siblings = 5            # range 1-1000
>
>
> Would it just be quicker to run a JOIN statement to a temp file and then
> reinsert?
> TIA Patrick
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>


--------------enigB102F44065A4841D42023663
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC9LxlJdeBCYSNAAMRAtL9AJ98wz0ruVi1wics+ko476ylNgbT3QCffWN8
arXwBSyM/BCE48I0BmunjUA=
=KQQn
-----END PGP SIGNATURE-----

--------------enigB102F44065A4841D42023663--