Suggestions on an update query

Поиск
Список
Период
Сортировка
От Campbell, Lance
Тема Suggestions on an update query
Дата
Msg-id B10E6810AC2A2F4EA7550D072CDE8760197DC6@SAB-FENWICK.sab.uiuc.edu
обсуждение исходный текст
Ответы Re: Suggestions on an update query  ("Campbell, Lance" <lance@uiuc.edu>)
Re: Suggestions on an update query  (Gregory Stark <stark@enterprisedb.com>)
Список pgsql-performance

PostgreSql version 8.2.4

Memory = 8 Gig

CPUs 1 dual core Zeon running at 3.0

 

I have a problem with an update query taking over 10 hours in order to run.   I rebooted my server.  I ran the SQL command “analyze”.  Could you please help me with any suggestions?  I have included the two tables involved in the update below as well as the indexes I am using. 

 

The table result_entry contains 17,767,240 rows and the table question_number contains 40,787.  Each row from the result_entry table will match to one and only one row in the table question_number using the fk_question_id field.  Each row from the question_number table matches to an average of 436 rows on the result_entry table.

 

CREATE TABLE question_number

(

  fk_form_id                    integer         not null,

  fk_question_id                integer         not null,

  question_number               integer not null,

  sequence_id                   integer not null

);

 

ALTER TABLE ONLY question_number ADD CONSTRAINT question_number_pkey PRIMARY KEY (fk_question_id);

CREATE INDEX question_number_index1 ON question_number USING btree (question_number);

 

 

CREATE TABLE result_entry (

    fk_result_submission_id integer NOT NULL,

    fk_question_id integer NOT NULL,

    fk_option_order_id integer NOT NULL,     

    value character varying,

    order_id integer NOT NULL,

    question_number integer

);

 

CREATE INDEX result_entery_index1 ON result_entry USING btree (fk_question_id);

 

 

update result_entry set question_number=question_number.question_number     

from question_number where result_entry.fk_question_id=question_number.fk_question_id;

 

 

 

explain update result_entry set question_number=question_number.question_number     

from question_number where result_entry.fk_question_id=question_number.fk_question_id;

 

                                   QUERY PLAN                                   

---------------------------------------------------------------------------------

 Hash Join  (cost=1437.71..1046983.94 rows=17333178 width=32)

   Hash Cond: (result_entry.fk_question_id = question_number.fk_question_id)

   ->  Seq Scan on result_entry  (cost=0.00..612216.78 rows=17333178 width=28)

   ->  Hash  (cost=927.87..927.87 rows=40787 width=8)

         ->  Seq Scan on question_number  (cost=0.00..927.87 rows=40787 width=8)

(5 rows)

 

 

 

Postgresql.conf settings:

 

shared_buffers = 1GB

work_mem = 10MB

max_fsm_pages = 204800

random_page_cost = 1.0

effective_cache_size = 8GB

 

 

Thanks for any help!

 

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] 8.3beta1 testing on Solaris
Следующее
От: "Campbell, Lance"
Дата:
Сообщение: Re: Suggestions on an update query