1

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.

0

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

SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / (1024/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

-1

Well, I suppose I can answer one of my questions. In vertica, delete operations do not remove rows from physical storage. So if I had a table MyTable with 10 000 rows and I did delete MyTable;, then MyTable will have the same size, because its rows were just marked as deleted but were not really purged.

As a result, if I wanna get actual row count I can't use projection_storage table for it.

  • This doesn't answer your own question – Kermit Mar 21 '16 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.