Message-ID: <1286.219.65.233.8.1051120485.squirrel@mail.trade-india.com>
Date: Wed, 23 Apr 2003 23:24: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: <200304231021.38040.josh@agliodbs.com>
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>
	<200304231021.38040.josh@agliodbs.com>
Importance: Normal
Cc: <mallah@trade-india.com>, <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

> 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  for you ... in
> fact, you generally get a notice like "Inserting missing FROM  clause for=
 table
> 'personal_account_details'".

Josh when i get that such  Notices the result of update is usually worng.
I do not get that notice for the SQL i posted.

>
> Am I reading your anaylze right?
i think u may have missed parts of it.

  I'm never 100% sure with 7.2 ... you *are*  updating 150,000
> rows?

7.2 u mean ,  pgsql 7.2 ?  No i am running pgsql 7.3.2

yes i did update 1,50,000 rows that time.

>
> If I'm right, then the query is using a seq scan because it's faster than=
 an  index scan for a
> large number of rows.   You can always test this by runninng  the query a=
fter an SET
> ENABLE_SEQSCAN =3D FALSE; generally the resulting query  will take 5-10 t=
imes as long.


Yes as explained in docs (somewhere ) and by many ppl seq scan is faster wh=
en
majority of  the rows are stisfy the search  criteria.

my botheration was that if pgsql were to search the personal_account_detail=
s with userid
(which is incidently the pkey there) why would it not use the uniq  index  ?
logically the query shud be looking the coutry feild from the personal_acco=
unt_details
for every userid (row) encoutered in email_bank table.

may be i am wrong here in correctly under standing the  explain output.

tradein_clients=3D# explain UPDATE email_bank set country=3Dpersonal_accoun=
t_details.country FROM
personal_account_details where email_bank.userid > 0 and
email_bank.userid=3Dpersonal_account_details.userid and ( email_bank.countr=
y <>
personal_account_details.country or email_bank.country IS NULL );          =
                                QUERY PLAN
---------------------------------------------------------------------------=
-------------------
 Hash Join  (cost=3D14497.06..37806.55 rows=3D186849 width=3D144)
   Hash Cond: ("outer".userid =3D "inner".userid)
   Join Filter: (("outer".country <> "inner".country) OR ("outer".country I=
S NULL))
   ->  Seq Scan on email_bank  (cost=3D0.00..16268.10 rows=3D216658 width=
=3D130)
         Filter: (userid > 0)
   ->  Hash  (cost=3D14113.45..14113.45 rows=3D153445 width=3D14)
         ->  Seq Scan on personal_account_details  (cost=3D0.00..14113.45 r=
ows=3D153445 width=3D14)
(7 rows)

I am bothered abt the second seq scan , sorry i do not understand "Hash" is=
 there any docs that
explain me that ?

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

my WAL logs (pg_xlog) are already in a dedicated disk [ buts thats a differ=
ent topic ]

>
> 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
>     and email_bank.country <> personal_account_details.country
> 	or email_bank.country IS NULL;

Yes It took very less , because there were actually very less rows to updat=
e .
I agree the  "email_bank.country <> personal_account_details.country 	or em=
ail_bank.country IS NULL"
was a neat thing to do in last query which i didnt'   :-(

this time since most of the rows were already equal becoz i updated them a =
little while
back with that 5 mins query ;-)

anyway could u plez explain the second "seq scan part" ?


HERE WAS THE EXPLIAN ANALYZE OUTPUT:

hey dont you think the parentheis i added were necessary for the query
to update proper rows ? becoz the  "or" part could have evaluated  to true =
even for
no matching userids resulting in update of unecessary rows.


tradein_clients=3D# begin work ; explain analyze UPDATE email_bank set
country=3Dpersonal_account_details.country FROM personal_account_details wh=
ere email_bank.userid > 0
and email_bank.userid=3Dpersonal_account_details.userid and ( email_bank.co=
untry <>
personal_account_details.country or email_bank.country IS NULL );BEGIN
Time: 720.04 ms
                                                                  QUERY PLAN
---------------------------------------------------------------------------=
------------------------------------------------------------------- Hash Jo=
in  (cost=3D14497.06..37806.55 rows=3D186849 width=3D144) (actual time=3D58=
74.10..8754.98
 rows=3D1033 loops=3D1)   Hash Cond: ("outer".userid =3D "inner".userid)
   Join Filter: (("outer".country <> "inner".country) OR ("outer".country I=
S NULL))
   ->  Seq Scan on email_bank  (cost=3D0.00..16268.10 rows=3D216658 width=
=3D130) (actual
   time=3D0.83..1361.35 rows=3D156669 loops=3D1)         Filter: (userid > =
0)
   ->  Hash  (cost=3D14113.45..14113.45 rows=3D153445 width=3D14) (actual t=
ime=3D5855.59..5855.59 rows=3D0
   loops=3D1)         ->  Seq Scan on personal_account_details  (cost=3D0.0=
0..14113.45 rows=3D153445 width=3D14)
         (actual time=3D0.04..5550.93 rows=3D153466 loops=3D1) Total runtim=
e: 9370.74 msec
(8 rows)


>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)--------------------------- =
TIP 4: Don't 'kill -9'
> the postmaster


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