Обсуждение: Run analyze on schema
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.Thanks,-Suresh Raja
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
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