Message-ID: <43ECFF0B.80905@obviously.com>
Date: Fri, 10 Feb 2006 13:00:59 -0800
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050921
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Very slow updates when using IN syntax subselect
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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)