Is there a SYSTEM table with row counts?

[Julie]

Hi y'all,

Is there a SYSTEM table in Vertica that contains the current row counts for all tables in the database?

[JimKnicely]

Hi Juliette,

Of course the simplest way to get a table row count is via the COUNT aggregate function:

Code: Select all

dbadmin=> SELECT COUNT(*) FROM verticadb_wh.date_dim;
 COUNT
-------
  4752
(1 row)

Remember that in Vertica that tables do not actually contain data; its their associated projections that do! Therefore, another method is via the ROW_COUNT column of the PROJECTION_STORAGE system table:

Code: Select all

dbadmin=> SELECT MAX(row_count) row_count
dbadmin->   FROM projection_storage
dbadmin->  WHERE anchor_table_schema = 'verticadb_wh'
dbadmin->    AND anchor_table_name = 'date_dim';
 row_count
-----------
      4752
(1 row)

Hope this helps!

[Julie]

Thank you! I'll create my own view having a column named row_count using your query to populate it!