Need Some Explanation of an EXPLAIN
От | Bill Thoen |
---|---|
Тема | Need Some Explanation of an EXPLAIN |
Дата | |
Msg-id | 48DD2636.4080105@gisnet.com обсуждение исходный текст |
Ответы |
Re: Need Some Explanation of an EXPLAIN
|
Список | 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)
В списке pgsql-general по дате отправления: