How to quickly get row count estimates for all tables


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


  • Options

    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

  • Options


    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.


  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    v_monitor.table_statistics seems right on 12.0.4 and 23.3.0

  • Options


    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?


  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    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

  • Options


    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?


  • Options
    Bryan_HBryan_H Vertica Employee Administrator

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

  • Options

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file