Re: Why is seq search preferred here by planner?

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'".

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

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 after an SET ENABLE_SEQSCAN = FALSE; generally the resulting query
will take 5-10 times as long.

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 locations of your files, the speed of your disk array, and your I/O
bandwidth.

How long does this take?

UPDATE email_bank set country=personal_account_details.countryFROM personal_account_detailswhere userid > 0 and
userid=personal_account_details.userid    and email_bank.country <> personal_account_details.countryor
email_bank.countryIS NULL;  

--
Josh Berkus
Aglio Database Solutions
San Francisco