Skip site navigation (1) Skip section navigation (2)

Re: Suggestions on an update query

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: "Campbell, Lance" <lance(at)uiuc(dot)edu>
Cc: "Campbell, Lance" <lance(at)uiuc(dot)edu>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Suggestions on an update query
Date: 2007-10-26 21:15:47
Message-ID: 20071026141547.70c9c1e0@scratch (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 26 Oct 2007 15:31:44 -0500
"Campbell, Lance" <lance(at)uiuc(dot)edu> 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/

In response to

Responses

pgsql-performance by date

Next:From: Jignesh K. ShahDate: 2007-10-26 21:45:19
Subject: Re: [HACKERS] 8.3beta1 testing on Solaris
Previous:From: Gregory StarkDate: 2007-10-26 21:12:37
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group