On Fri, 26 Oct 2007 15:31:44 -0500 "Campbell, Lance" <lance@xxxxxxxx> wrote: > I forgot to include an additional parameter I am using in > Postgresql.conf: > O.k. first, just to get it out of the way (and then I will try and help). Please do not top post, it makes replying contextually very difficult. > > PostgreSql version 8.2.4 > > Memory = 8 Gig > > CPUs 1 dual core Zeon running at 3.0 > O.k. first you might be grinding through your 20 checkpoint segments but in reality what I think is happening is you are doing foreign key checks against all of it and slowing things down. > > 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. > > You could disable the foreign key for the update and then reapply it. Joshua D. Drake > > 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 > > > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Attachment:
signature.asc
Description: PGP signature
