From: Joshua D. Drake <jd@commandprompt.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: Campbell, Lance <lance@uiuc.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Suggestions on an update query
Date: Fri, 26 Oct 2007 14:15:47 -0700
Message-ID: <20071026141547.70c9c1e0@scratch> (raw)
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760197DC7@SAB-FENWICK.sab.uiuc.edu>
References: <B10E6810AC2A2F4EA7550D072CDE8760197DC6@SAB-FENWICK.sab.uiuc.edu>
<B10E6810AC2A2F4EA7550D072CDE8760197DC7@SAB-FENWICK.sab.uiuc.edu>
On Fri, 26 Oct 2007 15:31:44 -0500
"Campbell, Lance" <lance@uiuc.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/Attachments:
signature.asc (application/pgp-signature, 188B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jd@commandprompt.com, lance@uiuc.edu
Subject: Re: Suggestions on an update query
In-Reply-To: <20071026141547.70c9c1e0@scratch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox