How to speed up vertica's count query??

HyeontaeJu

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?

Vertica_Curtis

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_C

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