To display an up to date “current” row count of a table, we can run the classic SELECT COUNT(*) statement.
Example:
yugabyte=# \timing on
Timing is on.
yugabyte=# CREATE TABLE some_data AS SELECT CEIL(RANDOM()*1000) x FROM generate_series(1, 5000000) x;
SELECT 5000000
Time: 224423.299 ms (03:44.423)
yugabyte=# SELECT COUNT(*) FROM some_data;
count
---------
5000000
(1 row)
Time: 24454.292 ms (00:24.454)
Unfortunately, the SQL statement took almost 25 seconds to execute.
If an up to date “current” table row count is not needed, we can get an “estimated” row count from either the PG_STAT_ALL_TABLES, PG_STAT_USER_TABLES or PG_CLASS system table in a few microseconds.
But for that to work, we will have to regularly collect statistics on the table via the ANALYZE command – perhaps via a nightly batch job.
Example:
LINE 1: ANALYZE some_data;
^
HINT: Set 'ysql_beta_features' yb-tserver gflag to true to suppress the warning for all beta features.
ANALYZE
Time: 27503.666 ms (00:27.504)
yugabyte=# SELECT relname, n_live_tup row_count FROM pg_stat_all_tables WHERE relname = 'some_data';
relname | row_count
-----------+-----------
some_data | 5000000
(1 row)
Time: 18.127 ms
yugabyte=# SELECT relname, n_live_tup row_count FROM pg_stat_user_tables WHERE relname = 'some_data';
relname | row_count
-----------+-----------
some_data | 5000000
(1 row)
Time: 15.004 ms
yugabyte=# SELECT reltuples::INT row_count FROM pg_class WHERE relname = 'some_data';
row_count
-----------
5000000
(1 row)
Time: 9.698 ms
Note that the ANALYZE command is currently a beta feature in YugabyteDB 2.13.
Have Fun!