Page 1 of 1

How to analyze statistics on all tables in a schema?

Posted: Thu Dec 01, 2016 8:01 pm
by Brett
I am trying to analyze the stats on all tables in a schema. Is this possible using the analyze statistics function? I tried the following:

Code: Select all

dbadmin=> SELECT analyze_statistics('hippies');
ERROR 2065:  analyze_statistics: Invalid table/projection/column hippies

dbadmin=> SELECT analyze_statistics('hippies.*');
ERROR 2065:  analyze_statistics: Invalid table/projection/column hippies.*

Re: How to analyze statistics on all tables in a schema?

Posted: Thu Dec 01, 2016 8:16 pm
by JimKnicely
Yeah, you can't do that (yet).

There are work-arounds, though. Here's one... Say I have the following tables in the schema "test":

Code: Select all

dbadmin=> \dt test.*
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 test   | t1   | table | dbadmin |
 test   | t2   | table | dbadmin |
 test   | t3   | table | dbadmin |
(3 rows)
I can analyze them all like this:

Code: Select all

dbadmin=> \! vsql -atc "select 'select analyze_statistics(''' || table_schema || '.' || table_name || ''');' from tables where table_schema = 'test';" | vsql
               ?column?
---------------------------------------
 select analyze_statistics('test.t1');
 select analyze_statistics('test.t2');
 select analyze_statistics('test.t3');
(3 rows)

 analyze_statistics
--------------------
                  0
(1 row)

 analyze_statistics
--------------------
                  0
(1 row)

 analyze_statistics
--------------------
                  0
(1 row)