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