How to speed up vertica's count query??

HyeontaeJuHyeontaeJu Vertica Customer

in my table has the 287,000,000
for example)
select count(*) from [table]
the time is 4 sec,,,, How to speed up count query?

Answers

  • Put a live aggregate projection on it.
    That way, the count is calculated as data is loaded into it.

    But live aggregates don't support a straight count() (without a group by), so you'll need to pick something to group it by. I'd pick a low cardinality column, for example, country_cd. And then you could do count(), country_cd group by country_cd. To get the entire count, you'd just have to sum up the counts for all the countries. That would be ~100 rows, versus 280M rows. So, you'd be looking at something like 10 ms or something like that. Very fast.

  • Navin_CNavin_C Vertica Customer

    Try making sure the stats on table are updated.
    If stats is updated, then count should be faster.

Leave a Comment

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