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  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Dianne Yumul
Дата:
Сообщение: Re: Stroring html form settings
Следующее
От: "Scott Marlowe"
Дата:
Сообщение: Re: Need Some Explanation of an EXPLAIN