Re: Very slow updates when using IN syntax subselect

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.04rows=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.06rows=1 width=616)  Join Filter: ("outer".event_id = "inner".event_id)  ->  Seq Scan on event
(cost=0.00..0.00rows=1 width=616)  ->  Nested Loop  (cost=0.00..3.04 rows=1 width=8)        ->  Seq Scan on event
(cost=0.00..0.00rows=1 width=408)              Filter: (reconciled = false)        ->  Index Scan using
token_token_number_keyon 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