Page MenuHomeSoftware Heritage
Paste P296

count total size of indexes of the db
ActivePublic

Authored by ardumont on Sep 6 2018, 2:43 PM.
begin;
create or replace function swh_count_total_indexes_size()
returns bigint
language plpgsql
as $$
declare
s bigint;
l bigint;
t text;
begin
s := 0;
for t in
select distinct tablename from pg_tables where schemaname='public'
loop
select pg_indexes_size(t) into l;
s := s + l;
end loop;
return s;
end
$$;
select pg_size_pretty(swh_count_total_indexes_size());
rollback;

Event Timeline

softwareheritage=> begin;
BEGIN
softwareheritage=>
softwareheritage=> create or replace function swh_count_total_indexes_size()
softwareheritage-> returns bigint
softwareheritage-> language plpgsql
softwareheritage-> as $$
softwareheritage$> declare
softwareheritage$> s bigint;
softwareheritage$> l bigint;
softwareheritage$> t text;
softwareheritage$> begin
softwareheritage$> s := 0;
softwareheritage$>
softwareheritage$> for t in
softwareheritage$> select distinct tablename from pg_tables where schemaname='public'
softwareheritage$> loop
softwareheritage$> select pg_indexes_size(t) into l;
softwareheritage$> s := s + l;
softwareheritage$> end loop;
softwareheritage$>
softwareheritage$> return s;
softwareheritage$> end
softwareheritage$> $$;
CREATE FUNCTION
softwareheritage=>
softwareheritage=> select pg_size_pretty(swh_count_total_indexes_size());

pg_size_pretty

4233 GB
(1 row)

softwareheritage=>
softwareheritage=> rollback;
ROLLBACK