Re: Why is seq search preferred here by planner?

Mallah,

> tradein_clients=# begin work ; explain analyze UPDATE email_bank set
> country=personal_account_details.country FROM personal_account_details where
email_bank.userid > 0
> and email_bank.userid=personal_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 them.

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

How often do you do this query? If it's frequent, then running my version 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 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
the query, and it should finish in < 30 seconds, and that should be good
enough, yes?

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.

--
-Josh Berkus
Aglio Database Solutions
San Francisco