Re: Very slow updates when using IN syntax subselect

Tom Lane wrote:
> Bryce Nesbitt <bryce1(at)obviously(dot)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