Re: Run analyze on schema

Suresh Raja <suresh(dot)rajaabc(at)gmail(dot)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(dot)consulting(at)comcast(dot)net
p: 312.241.7800