|
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 217.333.0382 http://webservices.uiuc.edu |
