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!
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.