From: Josh Berkus <josh@agliodbs.com>
To: mallah@trade-india.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Why is seq search preferred here by planner?
Date: Wed, 23 Apr 2003 10:21:38 -0700
Message-ID: <200304231021.38040.josh@agliodbs.com> (raw)
In-Reply-To: <1248.219.65.233.8.1051117946.squirrel@mail.trade-india.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>
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.country
FROM personal_account_details
where userid > 0 and userid=personal_account_details.userid
and email_bank.country <> personal_account_details.country
or email_bank.country IS NULL;
--
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: <200304231021.38040.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