How to quickly get row count estimates for all tables

You can use the v_catalog.estimate_row_count system view in Vertica to quickly get row count estimates for all tables. It provides estimated row counts for each table or projection. Exclude ROS containers and temporary tables using a query

David,

Thank you very much for info.
System view name is not correct:

dbadmin@xxx [PROD]: ~ $ /opt/vertica/bin/vsql -c "select * from v_catalog.estimate_row_count;"
ERROR 4568:  Relation "v_catalog.estimate_row_count" does not exist

Can you check system view name? I am on v 12.0.4.

Sergey

v_monitor.table_statistics seems right on 12.0.4 and 23.3.0

Bryan,

Big thanks for a hint! That table definitely would do exactly what I want.

Unfortunately, v_monitor.table_statistics does not have schema name on 12.0.4, and not really usable.

Any suggestions?

Sergey

This will add table_schema to the view:
select * from v_monitor.table_statistics ts join tables t on ts.table_oid = t.table_id
You could create a custom view including this join based the system view definition found in vs_system_views

Brian,

Big thanks for a hint!

Unfortunately, table v_monitor.table_statistics do not have columns table_oid or table_id.

It does have logical_stats_oid. Anywhere I can join using this column, to get schema name?

Sergey

Really? On my system:
d2=> select * from table_statistics where table_name = 'test';
logical_stats_oid | table_oid | schema_oid | table_name | min_partition_key | max_partition_key | row_count | is_row_count_valid | stat_collection_time
-------------------+-------------------+-------------------+------------+-------------------+-------------------+-----------+--------------------+------------------------------
45035999938235542 | 45035999938233428 | 45035999938231340 | test | | | 8 | t | 2023-06-12 23:55:23.44553-04

Try the view definition:
d2=> select * from vs_system_views where view_name = 'table_statistics';
create view "v_monitor"."table_statistics" as select ls."logical_stats" as "logical_stats_oid",
t."oid" as "table_oid",
t."schema" as "schema_oid",
t."name" as "table_name",
ls."min_val" as min_partition_key,
ls."max_val" as max_partition_key,
ls."row_count",
ls."is_row_count_valid",
ls."stat_collection_time"
from "v_internal"."vs_logical_statistics" as ls
left join "v_internal"."vs_tables" as t
on ls."table_oid" = t."oid"
where ls."is_row_count_valid";

=> select * from v_monitor.table_statistics limit 1;
logical_stats_oid | table_name | min_partition_key | max_partition_key | row_count | stat_collection_time
-------------------+-----------------+-------------------+-------------------+-----------+-------------------------------
45035996273851120 | flex_test1_keys | | | 1 | 2023-07-08 08:44:01.907284-04
(1 row)

I am on special patch derived from 12.0.4-8. Most likely, you are on 12.0.4-10, probably it was fixed there.

I am going to v 12.0.4-12 when it will be released in two weeks, I will let you know if it is fixed there.