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.
