DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=O8QWOI9qTPC6DDUfJMN5KXNLfJUMRKxFUiPiMLD0bHOeeULUvpk1hZHWBQXe56hA/E
	qAuwQTJQIYsDfS9KB04RJTbknSdr5eMvG9fOWQdRJ56GPd9vpOEaJBNh0yfXFhj/3k45
	0EFvzUTmohZN20p2pzFWjDiug2jrIGG6GkIcw=
MIME-Version: 1.0
In-Reply-To: <5ab13580910291050v26bea7e5j83d1a07f11956d39@mail.gmail.com>
References: <5ab13580910291050v26bea7e5j83d1a07f11956d39@mail.gmail.com>
Date: Sat, 31 Oct 2009 18:08:06 -0600
Message-ID: <dcc563d10910311708s281a81deg81a3968cbc4073ab@mail.gmail.com>
Subject: Re: Speed up UPDATE query?
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Oct 29, 2009 at 11:50 AM, Lee Hachadoorian
<lee.hachadoorian@gmail.com> wrote:
> I'm trying to update several tables (all child tables of the same
> parent), and as the number of records increases, the length of time it
> takes to run the update is shooting up exponentially. I have imported
> the new data to an import table, and then join the import table to the
> update table. The update statement looks like:
>
> UPDATE
> =A0 =A0 =A0 =A0household_2000 h
> SET
> =A0 =A0 =A0 =A0hhincome =3D new_hhincome
> FROM (
> =A0 =A0 =A0 =A0SELECT
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0serial, new_hhincome
> =A0 =A0 =A0 =A0FROM
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0import
> =A0 =A0 =A0 =A0WHERE
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0year =3D 2000
> =A0 =A0 =A0 =A0) r
> WHERE
> =A0 =A0 =A0 =A0h.serial =3D r.serial
>
> household_2000 is a child table of a household table that, as you
> might guess, only contains records from the year 2000. I am putting a
> year =3D 2000 restriction on the import table and then linking on the
> unique identifier.
>
> For different child tables, this is how long the update takes to run
> (numbers are approximate):
>
> Records =A0 =A0 =A0 =A0 Cost (via EXPLAIN) =A0 =A0 =A0 =A0 =A0 =A0Actual =
time
> 460,000 =A0 =A0 =A0 =A0 300,000 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A023 seconds
> 510,000 =A0 =A0 =A0 =A0 320,000 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A026 seconds
> 1.2 million =A0 =A0 =A0670,000 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A03:16
> 1.3 million =A0 =A0 =A0820,000 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A03:25
> 6.2 million =A0 =A0 =A02.7 million =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 ~2.5 hours
>
> So, the cost estimate given by EXPLAIN seems to be roughly
> proportional to the number of records in the dataset, but the actual
> time it takes to run seems to increase faster than the cost, even for
> the small and medium tables, and shoots through the roof for the large
> tables. Since I need to run this on additional child tables that are
> larger (the largest is 14 million records), I want to know what I can
> do to speed up the query.
>
> Here's the EXPLAIN for the query. Note that the query plan is the same
> for the small, medium, and large tables.
>
> Hash Join =A0(cost=3D1268532.36..2379787.06 rows=3D5465837 width=3D1128)
> =A0Hash Cond: (import_6_17_rev_hh.serial =3D h.serial)
> =A0-> =A0Bitmap Heap Scan on import_6_17_rev_hh
> (cost=3D126551.72..308495.69 rows=3D5465837 width=3D8)
> =A0 =A0 =A0 =A0Recheck Cond: (year =3D 1990)
> =A0 =A0 =A0 =A0-> =A0Bitmap Index Scan on import_6_17_rev_hh_pkey
> (cost=3D0.00..125185.26 rows=3D5465837 width=3D0)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (year =3D 1990)
> =A0-> =A0Hash =A0(cost=3D295596.06..295596.06 rows=3D5527406 width=3D1124=
)
> =A0 =A0 =A0 =A0-> =A0Seq Scan on household_1990 h =A0(cost=3D0.00..295596=
.06
> rows=3D5527406 width=3D1124)

Any chance of getting the output of explain analyze for a fast and a
slow run of this query?