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.

Vikora
  • 11
  • 2
  • 4

2 Answers2

2

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

Yair Halberstadt
  • 145
  • 8
santosh tiwary
  • 191
  • 3
-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.

Vikora
  • 11
  • 2
  • 4