Slow inserts with indentity column

ttvvttvv Community Edition User
edited June 2021 in General Discussion

Hi all,

I'm having issue with slow inserts from joined tables into one destination table. I'm using INSERT / SELECT, not row by row insert or similar.

My destination table has 80 columns with IDENTITY(0,1,1) as primary key.

Insert for just 400 records takes 5 seconds.
SELECT statement on joined tables is fast, but when I combine it with INSERT it takes 5 seconds for this amount of records.

Anyway, if I remove IDENTITY column from destination table, it takes just 200 ms.

Is there any proper way how to handle this, because gap between those two results is so huge.

I'm using Vertica inside Docker on Windows environment.


Best Answer

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited June 2021 Answer ✓


    The problem isn't the IDENTITY per se, it's that you disabled the cache! When you create an IDENTITY column Vertica creates a sequence to back it. You've disabled the cache on the sequence, so for each row inserted Vertica has to grab a catalog lock to generate a new sequence value!

    Here is the doc link AUTO_INCREMENT and IDENTITY Sequences.

    Note that the third parameter you passed to IDENTITY was 1. A value of 0 or 1 specifies to disable caching.

    For further details on this topic, check out the doc page Sequence Caching!

    And here's a link to a Vertica Quick Tip titled "Be Careful with the Sequence CACHE Value"...


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file