How to quickly get row count estimates for all tables

Sergey_Cherepan_1

Hi,

Vertica periodically run AnalyzeRowCount on all tables.
At least, database parameter AnalyzeRowCountInterval, default 84600, suggest what database is doing.

Is there a system view that allow very quickly to get this value for all tables?

I know I can export statistics into XML and get estimated row count value, but that takes forever and it is per per table.

I am currently getting row count (and disk size) per projection using system view projection_storage, and it is "expensive" on huge clusters with huge data.

Would be very nice if you can point me to some internal system view that will give me very quickly current statistics on row count per projection or per table. Key here - very quickly. I am pretty sure system view with this info does exist.

I am on v 12.0.4.

Thank you
Sergey

david222

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

Sergey_Cherepan_1

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

Bryan_H

v_monitor.table_statistics seems right on 12.0.4 and 23.3.0

Sergey_Cherepan_1

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

Bryan_H

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

Sergey_Cherepan_1

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

Bryan_H

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";

Sergey_Cherepan_1

=> 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.