DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=rhJvfnRayulcUk4bN+RB+zgHq0FJBgUOfP4b6oTIuIQW1Q/Bo2nvNkNuaUwb5JM7zu
	Q3F9/zrXmmCjhwhJXCyl+8Rhhj0Yi3/9oBVrIEL/4CPTUfaHa9QYxUArscE0yMYbKiud
	4ogp4MdLqO7TUmIyAZlyyePL4m7FbOH+JgP5A=
MIME-Version: 1.0
Date: Thu, 29 Oct 2009 13:50:38 -0400
Message-ID: <5ab13580910291050v26bea7e5j83d1a07f11956d39@mail.gmail.com>
Subject: Speed up UPDATE query?
From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

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
	household_2000 h
SET
	hhincome = new_hhincome
FROM (
	SELECT
		serial, new_hhincome
	FROM
		import
	WHERE
		year = 2000
	) r
WHERE
	h.serial = 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 = 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         Cost (via EXPLAIN)            Actual time
460,000         300,000                            23 seconds
510,000         320,000                            26 seconds
1.2 million      670,000                            3:16
1.3 million      820,000                            3:25
6.2 million      2.7 million                         ~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  (cost=1268532.36..2379787.06 rows=5465837 width=1128)
  Hash Cond: (import_6_17_rev_hh.serial = h.serial)
  ->  Bitmap Heap Scan on import_6_17_rev_hh
(cost=126551.72..308495.69 rows=5465837 width=8)
        Recheck Cond: (year = 1990)
        ->  Bitmap Index Scan on import_6_17_rev_hh_pkey
(cost=0.00..125185.26 rows=5465837 width=0)
              Index Cond: (year = 1990)
  ->  Hash  (cost=295596.06..295596.06 rows=5527406 width=1124)
        ->  Seq Scan on household_1990 h  (cost=0.00..295596.06
rows=5527406 width=1124)

Any help will be appreciated. Thanks,

--Lee

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center