Very slow updates when using IN syntax subselect

From: Bryce Nesbitt <bryce1(at)obviously(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Very slow updates when using IN syntax subselect
Date: 2006-02-10 21:00:59
Message-ID: 43ECFF0B.80905@obviously.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

If I do:
select event_id from event join token using
(token_number) where token_status=50 and
reconciled=false limit 1;
Then:
update event set reconciled=true where event_id={XXX};

It returns in about a second, or less. But If I do the same thing with
the IN syntax:

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. Any clues what might be going on? Help would be much
appreciated. I'm not seeing this on all my DB's... just the important ones.

PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2006-02-10 21:32:56 Re: Very slow updates when using IN syntax subselect
Previous Message Chris Browne 2006-02-10 20:11:47 Re: unique constraint instead of primary key? what