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);
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