Run analyze on schema

Suresh Raja <suresh.rajaabc@gmail.com> writes:

Hi All:

Does postgresql support schema analyze.  I could not find
analyze schema anywhere.  Can we create a function to run
analyze and reindex on all objects in the schema.  Any
suggestions or ideas.

Yes "we" certainly can...

begin;

create function foo(sch text)
returns void as

$$
declare sql text;

begin

for sql in
select format('analyze verbose %s.%s', schemaname, tablename) from pg_tables
where schemaname = sch

loop execute sql; end loop;

end
$$ language plpgsql;

select foo('public');
select foo('pg_catalog');

-- Enjoy!!

Thanks,
-Suresh Raja

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jun 22, 2015 at 6:53 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:

Suresh Raja <suresh.rajaabc@gmail.com> writes:

Hi All:

Does postgresql support schema analyze. I could not find
analyze schema anywhere. Can we create a function to run
analyze and reindex on all objects in the schema. Any
suggestions or ideas.

Yes "we" certainly can...

begin;

create function foo(sch text)
returns void as

$$
declare sql text;

begin

for sql in
select format('analyze verbose %s.%s', schemaname, tablename) from
pg_tables
where schemaname = sch

loop execute sql; end loop;

end
$$ language plpgsql;

select foo('public');
select foo('pg_catalog');

-- Enjoy!!

Thanks,
-Suresh Raja

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Thanks Jerry!

I too your example and added exception handling into it.

Thanks