Here http://www.postgresql.org/message-id/24278.1352922...@sss.pgh.pa.us there
was some talk about init_sequence being a bottleneck when many sequences
are used in a single backend.

The attached I think implements what was talked about in the above link
which for me seems to double the speed of a currval() loop over 30000
sequences. It goes from about 7 seconds to 3.5 on my laptop.

I thought I would post the patch early to see if this is actually wanted
before I do too much more work on it.

My implementation maintains using the linear list for sequences up to a
defined threshold (currently 32) then it moves everything over to a
hashtable and free's off the list.

A more complete solution would contain regression tests to exercise the
hash table code.

I know there is a desire to move sequences over to a single table still,
but I see this as a separate patch and storing current values in a hash
table for each backend should still be a win even if/when the single table
stuff gets implemented.

Regards

David Rowley
CREATE FUNCTION create_seq(n integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
BEGIN
    WHILE n > 0 LOOP
        EXECUTE 'CREATE SEQUENCE test' || CAST(n AS TEXT);
      n := n - 1;
    END LOOP;
    RETURN 0;
END
$$;

CREATE FUNCTION currval_seq(n integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
    WHILE n > 0 LOOP
        EXECUTE $$SELECT currval('test$$ || CAST(n AS TEXT) || $$')$$;
      n := n - 1;
    END LOOP;
    RETURN 0;
END
$_$;

CREATE FUNCTION drop_seq(n integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
BEGIN
    WHILE n > 0 LOOP
        EXECUTE 'DROP SEQUENCE test' || CAST(n AS TEXT);
      n := n - 1;
    END LOOP;
    RETURN 0;
END
$$;

CREATE OR REPLACE FUNCTION nextval_seq(n integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
    WHILE n > 0 LOOP
        EXECUTE $$SELECT nextval('test$$ || CAST(n AS TEXT) || $$')$$;
        n := n - 1;
    END LOOP;
    RETURN 0;
END
$_$;


SELECT create_seq(10000);
SELECT nextval_seq(10000);
SELECT currval_seq(10000);

Attachment: hashtab_seq_v0.1.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to