X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 2FEA84762E1
	for <pgsql-sql@postgresql.org>; Wed, 23 Apr 2003 14:21:23 -0400 (EDT)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 617344762E0
	for <pgsql-sql@postgresql.org>; Wed, 23 Apr 2003 14:21:21 -0400 (EDT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2982710; Wed, 23 Apr 2003 11:21:17 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: 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 11:20:56 -0700
User-Agent: KMail/1.4.3
Cc: <mallah@trade-india.com>, <pgsql-sql@postgresql.org>
References: <1220.219.65.235.51.1051114881.squirrel@mail.trade-india.com>
	<200304231021.38040.josh@agliodbs.com>
	<1286.219.65.233.8.1051120485.squirrel@mail.trade-india.com>
In-Reply-To: <1286.219.65.233.8.1051120485.squirrel@mail.trade-india.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304231120.56699.josh@agliodbs.com>
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/345
X-Sequence-Number: 13046

Mallah,

> tradein_clients=3D# begin work ; explain analyze UPDATE email_bank set
> country=3Dpersonal_account_details.country FROM personal_account_details =
where=20
email_bank.userid > 0
> and email_bank.userid=3Dpersonal_account_details.userid and (=20
email_bank.country <>
> personal_account_details.country or email_bank.country IS NULL );BEGIN

Ooops, yeah, you're correct ... the parens are required, I just forgot them.

The reason that the planner is using a seq scan on personal_account_details=
 is=20
the same as the reason for using  a seq scan on email_bank; the number of=
=20
rows which match the condition, about 150,000.   With that many qualifying=
=20
rows, a seq scan is faster.

How often do you do this query?  If it's frequent, then running my version =
of=20
the query with two new indexes -- one on email_bank.userid,=20
email_bank.country, and one on personal_account_details.userid, country --=
=20
would be a interesting test with my version of the query.  Though I'm not=
=20
sure about the email_bank.country IS NULL condition; workarounds, anyone?

If you run this query only once a day, don't worry about it; run my version=
 of=20
the query, and it should finish in < 30 seconds, and that should be good=20
enough, yes?

Oh, and a "Hash" is a "Hash Join" -- where the DB basically throws all of t=
he=20
rows from both tables in a big mess and picks out the ones that match.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco