In YSQL the ANALYZE command collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. These statistics help the query planner to determine the most efficient execution plans for queries.
In addition to populating the
pg_statistic system catalog, the reltuples column in the pg_class system table is updated with the table’s row count at the time the ANALYZE command was executed. You can ANALYZE a table one at a time…
Example:
yugabyte=# CREATE TABLE tab (c1 INT);
CREATE TABLE
yugabyte=# INSERT INTO tab SELECT generate_series(1, 10000);
INSERT 0 10000
yugabyte=# SELECT relnamespace::regnamespace::text schema_name, relname table_name, reltuples table_rows FROM pg_class WHERE relkind = 'r' AND relowner = 'yugabyte'::regrole;
schema_name | table_name | table_rows
-------------+------------+------------
public | tab | 0
(1 row)
yugabyte=# ANALYZE tab;
ANALYZE
yugabyte=# SELECT relnamespace::regnamespace::text schema_name, relname table_name, reltuples table_rows FROM pg_class WHERE relkind = 'r' AND relowner = 'yugabyte'::regrole;
schema_name | table_name | table_rows
-------------+------------+------------
public | tab | 10000
(1 row)
Or you can ANALYZE all of the tables in the current database by simply using ANALYZE all by itself.
yugabyte=# SELECT current_database();
current_database
------------------
yugabyte
(1 row)
yugabyte=# CREATE TABLE t1 (c1 INT);
CREATE TABLE
yugabyte=# INSERT INTO t1 SELECT generate_series(1, 1000);
INSERT 0 1000
yugabyte=# CREATE SCHEMA s1;
CREATE SCHEMA
yugabyte=# CREATE TABLE s1.t1(c1 INT);
CREATE TABLE
yugabyte=# INSERT INTO s1.t1 SELECT generate_series(1, 1000);
INSERT 0 1000
yugabyte=# SELECT relnamespace::regnamespace::text schema_name, relname table_name, reltuples table_rows FROM pg_class WHERE relkind = 'r' AND relowner = 'yugabyte'::regrole;
schema_name | table_name | table_rows
-------------+------------+------------
public | tab | 10000
public | t1 | 0
s1 | t1 | 0
(3 rows)
yugabyte=# ANALYZE;
ANALYZE
yugabyte=# SELECT relnamespace::regnamespace::text schema_name, relname table_name, reltuples table_rows FROM pg_class WHERE relkind = 'r' AND relowner = 'yugabyte'::regrole;
schema_name | table_name | table_rows
-------------+------------+------------
public | tab | 10000
public | t1 | 1000
s1 | t1 | 1000
(3 rows)
Have Fun!
