Message-ID: <1331.219.65.233.8.1051123905.squirrel@mail.trade-india.com>
Date: Thu, 24 Apr 2003 00:21:45 +0530 (IST)
Subject: Re: Why is seq search preferred here by planner?
From: <mallah@trade-india.com>
To: <josh@agliodbs.com>
In-Reply-To: <200304231120.56699.josh@agliodbs.com>
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>
	<200304231120.56699.josh@agliodbs.com>
Importance: Normal
Cc: <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

> Mallah,
>
>> tradein_clients=3D# begin work ; explain analyze UPDATE email_bank set
>> country=3Dpersonal_account_details.country FROM personal_account_details=
 where
> email_bank.userid > 0
>> and email_bank.userid=3Dpersonal_account_details.userid and (
> 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 th=
em.
>
> The reason that the planner is using a seq scan on personal_account_detai=
ls is  the same as the
> reason for using  a seq scan on email_bank; the number of  rows which mat=
ch the condition,
> about 150,000.   With that many qualifying  rows, a seq scan is faster.


But there are two tables here , email_bank and personal_account_details
in personal account details only one row is supposed to match a given userid
as userid is the PKEY , why seq_scan there ? or am i getting the explain wr=
ong ?


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

Hey this query is not even part of my production system,

I am doing a massive import of all the mess i have created since
last year ;-) into a strcity normal form with lots of RIs and checks.


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

Not applicable!

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


Hmm..  although we have beaten around the bush  but my question is still no=
t answered
"why seq scan on a pkey " ??  (or am i getting the explain wrong )

anyway nevermind as  my update is already over and i have added an
FKEY  on the coutry feild to prevent collecting future mess.

Warm Regds
Mallah


>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)--------------------------- =
TIP 1: subscribe and
> unsubscribe commands go to majordomo@postgresql.org


-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/