BUG #8470: 9.3 locking/subtransaction performance regression - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8470
Logged by:          Oskari Saarenmaa
Email address:      os@ohmu.fi
PostgreSQL version: 9.3.0
Operating system:   Linux
Description:

The following code performs a lot slower on PostgreSQL 9.3.0 than on
PostgreSQL 9.2.4:


DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (id BIGSERIAL, vals BIGINT[]);
DO $$
DECLARE
    r_id BIGINT;
    n BIGINT;
BEGIN
    FOR n IN 1..1000 LOOP
        BEGIN
            SELECT id INTO r_id FROM tmp WHERE array_length(vals, 1) < 100
LIMIT 1 FOR UPDATE NOWAIT;
        EXCEPTION WHEN lock_not_available THEN
            r_id := NULL;
        END;
        IF r_id IS NULL THEN
            INSERT INTO tmp (vals) VALUES (ARRAY[n]::BIGINT[]);
        ELSE
            UPDATE tmp SET vals = array_append(vals, n::BIGINT) WHERE id =
r_id;
        END IF;
    END LOOP;
END;
$$;


PostgreSQL 9.3.0:
Time: 7278.910 ms


PostgreSQL 9.2.4:
Time: 128.008 ms


Removing the BEGIN/EXCEPTION/END block and just doing a 'SELECT FOR UPDATE'
for a suitable row is significantly slower in 9.3.0 (314.765 ms vs 118.894
ms on 9.2.4).  A 'SELECT' without a FOR UPDATE and BEGIN/EXCEPTION/END has
the same performance on 9.2.4 and 9.3.0.


I'm running 9.2.4 and 9.3.0 packages from apt.postgresql.org on a  Debian
Squeeze host.