From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Very slow updates when using IN syntax subselect
Date: Fri, 10 Feb 2006 21:12:30 -0800
Message-ID: <43ED723E.9080305@obviously.com> (raw)
In-Reply-To: <1682.1139607176@sss.pgh.pa.us>
References: <43ECFF0B.80905@obviously.com>
<1682.1139607176@sss.pgh.pa.us>
Tom Lane wrote:
> Bryce Nesbitt <bryce1@obviously.com> writes:
>
>> update event set reconciled=true where event_id in
>> (select event_id from event join token using (token_number)
>> where token_status=50 and reconciled=false LIMIT 1);
>>
>> On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU
>> forever.
>>
> What does EXPLAIN show for this and for the base query?
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..3.04 rows=1 width=8)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
Filter: (reconciled = false)
-> Index Scan using token_token_number_key on token
(cost=0.00..3.03 rows=1 width=11)
Index Cond: (("outer".token_number)::text =
(token.token_number)::text)
Filter: (token_status = 50)
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=0.00..3.06 rows=1 width=616)
Join Filter: ("outer".event_id = "inner".event_id)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=616)
-> Nested Loop (cost=0.00..3.04 rows=1 width=8)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
Filter: (reconciled = false)
-> Index Scan using token_token_number_key on token
(cost=0.00..3.03 rows=1 width=11)
Index Cond: (("outer".token_number)::text =
(token.token_number)::text)
Filter: (token_status = 50)
(9 rows)
select count(*) from event;
-----------
116226
stage=# select count(*) from token;
-------
8948
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: bryce1@obviously.com, tgl@sss.pgh.pa.us
Subject: Re: Very slow updates when using IN syntax subselect
In-Reply-To: <43ED723E.9080305@obviously.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