X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 864BC4762E0
	for <pgsql-sql@postgresql.org>; Wed, 23 Apr 2003 13:22:37 -0400 (EDT)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id EE3F7475F16
	for <pgsql-sql@postgresql.org>; Wed, 23 Apr 2003 13:22:26 -0400 (EDT)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2982548; Wed, 23 Apr 2003 10:22:22 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: <mallah@trade-india.com>
Subject: Re: Why is seq search preferred here by planner?
Date: Wed, 23 Apr 2003 10:21:38 -0700
User-Agent: KMail/1.4.3
Cc: <pgsql-sql@postgresql.org>
References: <1220.219.65.235.51.1051114881.squirrel@mail.trade-india.com>
	<200304231000.20578.josh@agliodbs.com>
	<1248.219.65.233.8.1051117946.squirrel@mail.trade-india.com>
In-Reply-To: <1248.219.65.233.8.1051117946.squirrel@mail.trade-india.com>
MIME-Version: 1.0
Message-Id: <200304231021.38040.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Spam-Status: No, hits=-32.4 required=5.0
	tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES,
	REPLY_WITH_QUOTES,USER_AGENT_KMAIL autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/328
X-Sequence-Number: 13029

Mallah,

> Hmm i am not running postgresql on a CRAY  :-)
>
>  that was the time for "begin work;"  since
> i am explain analysing an update i am putting it in transaction.
>
> the actualt time was nearing 300 secs which is 5 mins
>
> i did an explain of the UPDATE FROM variant of the query it has the same
> plan for it.

It's the same query.  When you forget the FROM, postgres tries to insert it=
=20
for you ... in fact, you generally get a notice like "Inserting missing FRO=
M=20
clause for table 'personal_account_details'".

Am I reading your anaylze right?  I'm never 100% sure with 7.2 ... you *are=
*=20
updating 150,000 rows?

If I'm right, then the query is using a seq scan because it's faster than a=
n=20
index scan for a large number of rows.   You can always test this by runnin=
ng=20
the query after an SET ENABLE_SEQSCAN =3D FALSE; generally the resulting qu=
ery=20
will take 5-10 times as long.

Your query delay time is updating 150,000 rows, period.   That's a *lot* of=
=20
disk activity, and it can only be improved by adjusting your postgresql.con=
f,=20
the disk locations of your files, the speed of your disk array, and your I/=
O=20
bandwidth.

How long does this take?

UPDATE email_bank set country=3Dpersonal_account_details.country
 FROM personal_account_details
 where userid > 0 and userid=3Dpersonal_account_details.userid=20
     and email_bank.country <> personal_account_details.country
	or email_bank.country IS NULL;=20

--=20
Josh Berkus
Aglio Database Solutions
San Francisco