From: | Bill Thoen <bthoen(at)gisnet(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Need Some Explanation of an EXPLAIN |
Date: | 2008-09-26 18:13:10 |
Message-ID: | 48DD2636.4080105@gisnet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to update a field in a table that has about 5 millin records
with a table that has about 3.5 million records. I've created indexes
for th a joined columns, but PostgreSQL 8.1 doesn't seem to want to use
them. This makes for a very slow update.
Below are descriptions of the two tables followed by the query plan that
my PostgreSQL wants to use. So I have two questions:
1.) Why won't it use the indexes?
2.) How can I make this update faster?
TIA,
Bill Thoen
Table "public.id2"
Column | Type | Modifiers
-----------+--------------+-----------
grower_id | integer |
fmid | character(7) |
fsa_id | character(9) |
Indexes:
"id2_fsa_is_key" UNIQUE, btree (fsa_id)
Table "public.growers"
Column | Type | Modifiers
--------------+-----------------------+-----------
grower_id | integer |
fsa_id | character(9) |
co_name | character varying(45) |
. . .
Indexes:
"grower_fsa_id_key" btree (fsa_id)
fsa=# EXPLAIN UPDATE growers
SET grower_id = id2.grower_id
FROM id2 WHERE growers.fsa_id = id2.fsa_id;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=70375.50..1603795.30 rows=6802720 width=355)
Hash Cond: ("outer".fsa_id = "inner".fsa_id)
-> Seq Scan on growers (cost=0.00..672373.20 rows=6802720 width=351)
-> Hash (cost=46249.20..46249.20 rows=1966920 width=44)
-> Seq Scan on id2 (cost=0.00..46249.20 rows=1966920 width=44)
(5 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2008-09-26 18:23:34 | Re: Need Some Explanation of an EXPLAIN |
Previous Message | Chris Browne | 2008-09-26 18:02:42 | Re: PostgreSQL future ideas |