Date: Fri, 26 Oct 2007 14:15:47 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Campbell, Lance" <lance@uiuc.edu>
Cc: "Campbell, Lance" <lance@uiuc.edu>, <pgsql-performance@postgresql.org>
Subject: Re: Suggestions on an update query
Message-ID: <20071026141547.70c9c1e0@scratch>
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760197DC7@SAB-FENWICK.sab.uiuc.edu>
References: <B10E6810AC2A2F4EA7550D072CDE8760197DC6@SAB-FENWICK.sab.uiuc.edu>
	<B10E6810AC2A2F4EA7550D072CDE8760197DC7@SAB-FENWICK.sab.uiuc.edu>
Organization: Joshua D. Drake
Mime-Version: 1.0
Content-Type: multipart/signed; boundary="Sig_/03QgO7hh3Yu20Ca+0pw4znS";
	protocol="application/pgp-signature"; micalg=PGP-SHA1

--Sig_/03QgO7hh3Yu20Ca+0pw4znS
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

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:=20
>=20

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.
>=20
> PostgreSql version 8.2.4
>=20
> Memory =3D 8 Gig
>=20
> CPUs 1 dual core Zeon running at 3.0
>=20

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.


>=20
> 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.
>=20
> =20


You could disable the foreign key for the update and then reapply it.

Joshua D. Drake


>=20
> CREATE TABLE question_number
>=20
> (
>=20
>   fk_form_id                    integer         not null,
>=20
>   fk_question_id                integer         not null,
>=20
>   question_number               integer not null,
>=20
>   sequence_id                   integer not null
>=20
> );
>=20
> =20
>=20
> ALTER TABLE ONLY question_number ADD CONSTRAINT question_number_pkey
> PRIMARY KEY (fk_question_id);
>=20
> CREATE INDEX question_number_index1 ON question_number USING btree
> (question_number);
>=20
> =20
>=20
> =20
>=20
> CREATE TABLE result_entry (
>=20
>     fk_result_submission_id integer NOT NULL,
>=20
>     fk_question_id integer NOT NULL,
>=20
>     fk_option_order_id integer NOT NULL,     =20
>=20
>     value character varying,
>=20
>     order_id integer NOT NULL,
>=20
>     question_number integer
>=20
> );
>=20
> =20
>=20
> CREATE INDEX result_entery_index1 ON result_entry USING btree
> (fk_question_id);
>=20
> =20
>=20
> =20
>=20
> update result_entry set
> question_number=3Dquestion_number.question_number
>=20
>=20
> from question_number where
> result_entry.fk_question_id=3Dquestion_number.fk_question_id;
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> explain update result_entry set
> question_number=3Dquestion_number.question_number     =20
>=20
> from question_number where
> result_entry.fk_question_id=3Dquestion_number.fk_question_id;
>=20
> =20
>=20
>                                    QUERY PLAN
>=20
>=20
> ------------------------------------------------------------------------
> ---------
>=20
>  Hash Join  (cost=3D1437.71..1046983.94 rows=3D17333178 width=3D32)
>=20
>    Hash Cond: (result_entry.fk_question_id =3D
> question_number.fk_question_id)
>=20
>    ->  Seq Scan on result_entry  (cost=3D0.00..612216.78 rows=3D17333178
> width=3D28)
>=20
>    ->  Hash  (cost=3D927.87..927.87 rows=3D40787 width=3D8)
>=20
>          ->  Seq Scan on question_number  (cost=3D0.00..927.87
> rows=3D40787 width=3D8)
>=20
> (5 rows)
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> Postgresql.conf settings:
>=20
> =20
>=20
> shared_buffers =3D 1GB
>=20
> work_mem =3D 10MB
>=20
> max_fsm_pages =3D 204800
>=20
> random_page_cost =3D 1.0
>=20
> effective_cache_size =3D 8GB
>=20
> =20
>=20
> =20
>=20
> Thanks for any help!
>=20
> =20
>=20
> =20
>=20
> Lance Campbell
>=20
> Project Manager/Software Architect
>=20
> Web Services at Public Affairs
>=20
> University of Illinois
>=20
> 217.333.0382
>=20
> http://webservices.uiuc.edu
>=20
> =20
>=20


--=20

      =3D=3D=3D The PostgreSQL Company: Command Prompt, Inc. =3D=3D=3D
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/


--Sig_/03QgO7hh3Yu20Ca+0pw4znS
Content-Type: application/pgp-signature; name=signature.asc
Content-Disposition: attachment; filename=signature.asc

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHIlkFATb/zqfZUUQRAnSeAKCKrXAiWmjifE/yzHtNJU/OY9SPtwCcDHII
ie4bHtTF27Fh7ZtPjON4+zk=
=ZLNo
-----END PGP SIGNATURE-----

--Sig_/03QgO7hh3Yu20Ca+0pw4znS--