Size, audit and rowcount of a table in Vertica

vertica

I need to find a compressed size, an audit and a row count for each table in some schemes.
For a compressed size and a row count I tried to use projection_storage:

SELECT anchor_table_schema, 
   anchor_table_name, 
   SUM(used_bytes),
   SUM(row_count)
FROM v_monitor.projection_storage 
GROUP BY anchor_table_schema, anchor_table_name

But for empty table (0 rows) I got non-zero SUM(used_bytes) and SUM(row_count). So this way doesn't work properly.
And I calculate an audit by select audit('table_name') for each table, but it's very slowly.
So my questions:

  1. Is there other way to find compressed size (and maybe a row count) of a table? Because I got 80000000 bytes instead of zero.

  2. Is there more faster way to calculate an audit? I know vertica calculates audits every day for each table, but I can't find where results is stored.

Best Answer

Using below query, you will get the size of Vertica tables.

SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / (1024 * 1024 * 1024) AS TABLE_SIZE_GB
FROM   v_monitor.projection_storage
GROUP  BY anchor_table_schema,
anchor_table_name
order  by sum(used_bytes) desc;

Check out the most important queries to check table size

https://www.orahow.com/2018/06/how-to-check-size-of-tables-in-vertica.html

Related Question