Re: Suggestions on an update query

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/