CODEWITHSUNDEEP
yugabytedb
FranckPachot
FranckPachot

Reputation: 597

How to gather statistics for all tables in a YugabyteDB schema

How to gather statistics for all tables in a YugabyteDB schema without having to write individual ANALYZE commands?

Upvotes: 0

Views: 129

Answers (1)

FranckPachot
FranckPachot

Reputation: 597

This generates the ANALYZE statement for all tables in the public schema, and runs it with psql gexec:

\set ECHO all
select format('analyse %I.%I /* current reltuples: %s */;', nspname, relname, reltuples) ddl
from pg_class natural join (select oid relnamespace, nspname from pg_namespace) s
where relkind = 'r' and nspname='public'
order by reltuples
\gexec

You can change it to another schema name.

On YugabyteDB database there's no autovacuum, and ANALYZE is still a beta feature (version 2.15). The following warning is expected:

WARNING:  'analyze' is a beta feature!
LINE 1: analyze ehr.access,ehr.attestation,ehr.attestation_ref,ehr.a...
        ^
HINT:  Set 'ysql_beta_features' yb-tserver gflag to true to suppress the warning for all beta features.

On YugabyteDB if you encounter ERROR: Timed out: Perform RPC (request call id ...) to ... timed out after ... you may want to increase yb_client_admin_operation_timeout_sec

Upvotes: 1

Related Questions