From: | "Campbell, Lance" <lance(at)uiuc(dot)edu> |
---|---|
To: | "Campbell, Lance" <lance(at)uiuc(dot)edu>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Suggestions on an update query |
Date: | 2007-10-26 20:31:44 |
Message-ID: | B10E6810AC2A2F4EA7550D072CDE8760197DC7@SAB-FENWICK.sab.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I forgot to include an additional parameter I am using in
Postgresql.conf:
checkpoint_segments = 30
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
________________________________
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Campbell,
Lance
Sent: Friday, October 26, 2007 3:27 PM
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORM] Suggestions on an update query
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pablo Alcaraz | 2007-10-26 20:37:40 | Speed difference between select ... union select ... and select from partitioned_table |
Previous Message | Campbell, Lance | 2007-10-26 20:26:46 | Suggestions on an update query |