From: Josh Berkus <josh@agliodbs.com>
To: mallah@trade-india.com
Cc: mallah@trade-india.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Why is seq search preferred here by planner?
Date: Wed, 23 Apr 2003 11:20:56 -0700
Message-ID: <200304231120.56699.josh@agliodbs.com> (raw)
In-Reply-To: <1286.219.65.233.8.1051120485.squirrel@mail.trade-india.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, mallah@trade-india.com
Subject: Re: Why is seq search preferred here by planner?
In-Reply-To: <200304231120.56699.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox