Be Careful with the Sequence CACHE Value

Jim Knicely authored this tip. The default session cache for a sequence is 250,000. Although you can change the cache value of a sequence, setting the value too low can adversely affect performance! Example:

dbadmin=> SELECT COUNT(*) FROM ten_thousand_records;
COUNT
-------
10000
(1 row)

dbadmin=> CREATE SEQUENCE default_cache;
CREATE SEQUENCE

dbadmin=> CREATE SEQUENCE non_default_cache CACHE 5;
CREATE SEQUENCE

dbadmin=> \timing on
Timing is on.

dbadmin=> CREATE TABLE t1 AS SELECT default_cache.nextval FROM ten_thousand_records;
CREATE TABLE
Time: First fetch (0 rows): 32.996 ms. All rows formatted: 33.013 ms

dbadmin=> CREATE TABLE t2 AS SELECT non_default_cache.nextval FROM ten_thousand_records;
CREATE TABLE
Time: First fetch (0 rows): 45314.645 ms. All rows formatted: 45314.663 ms

Note that it took 45 seconds to generate 10,000 new sequence values on a 3-node Vertica cluster where sequence cache was 5, but << 1 second when the cache was the default value. Have fun!