Be Careful with the Sequence CACHE Value

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners
edited June 21 in Vertica Tips

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!

Sign In or Register to comment.