Re: Why is seq search preferred here by planner?
От | |
---|---|
Тема | Re: Why is seq search preferred here by planner? |
Дата | |
Msg-id | 1331.219.65.233.8.1051123905.squirrel@mail.trade-india.com обсуждение исходный текст |
Ответ на | Re: Why is seq search preferred here by planner? (Josh Berkus <josh@agliodbs.com>) |
Ответы |
Re: Why is seq search preferred here by planner?
(Josh Berkus <josh@agliodbs.com>)
|
Список | pgsql-sql |
> 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. 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 wrong ? > > 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? 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 version 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 not 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/
В списке pgsql-sql по дате отправления: