I have been trying to run the following query for multiple tables:
SELECT count(*) as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_freespace('accounts');
What I have managed to write so far is:
do
$body$
declare
temprow record;
l_nop decimal;
l_pct decimal;
l_ratio decimal;
l_context text;
l_context_detail text;
tablex text;
begin
FOR temprow IN
select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')/1024/1024 as size_mb from information_schema.tables
where table_schema not in ('information_schema', 'pg_catalog') order by size_mb desc limit 100
LOOP
begin
raise notice 'for table %.% ', temprow.table_schema, temprow.table_name;
execute E'SELECT count(*), pg_size_pretty(cast(avg(avail) as bigint)), round(100 * avg(avail)/8192 ,2) FROM pg_freespace(\'$1.$2\')' using temprow.table_schema, temprow.table_name into l_nop, l_pct, l_ratio;
EXCEPTION
WHEN others THEN GET STACKED DIAGNOSTICS
l_context = PG_EXCEPTION_CONTEXT;
RAISE NOTICE 'ERROR:%', l_context;
end;
END LOOP;
end;
$body$;
Yet, I get
NOTICE: check1
NOTICE: for table public.pg_hist_stat_statements_history
NOTICE: ERROR:PL/pgSQL function inline_code_block line 18 at EXECUTE
I have tried different dynamic commands practices like format, but did not help. Also, tried to implement what I found in this question to get this point. What am I missing? Also, is it possible to write this PLPGSQL block as an SQL? My first thought was using the lateral
join but pg_freespace pipelined function, so it did not work.
Thanks!
There is no reason to use dynamic SQL here. pg_freespace
takes a table OID as its argument, which you can directly get from the first query. I'd use pg_catalog.pg_class
though not information_schema.tables
:
SELECT
relnamespace::regnamespace AS table_schema,
relname AS table_name,
pg_relation_size(oid)/1024/1024 as size_mb,
space.*
FROM pg_class,
LATERAL (
SELECT
count(*),
pg_size_pretty(cast(avg(avail) as bigint)),
round(100 * avg(avail)/8192 ,2)
FROM pg_freespace(oid)
) AS space
WHERE relkind = 'r'
AND relnamespace NOT IN ('information_schema'::regnamespace, 'pg_catalog'::regnamespace)
ORDER BY size_mb DESC
LIMIT 100
or more traditional with a GROUP BY
instead of a subquery:
SELECT
relnamespace::regnamespace AS table_schema,
relname AS table_name,
pg_relation_size(oid)/1024/1024 as size_mb,
count(*),
pg_size_pretty(cast(avg(avail) as bigint)),
round(100 * avg(avail)/8192 ,2)
FROM pg_class, LATERAL pg_freespace(oid)
WHERE relkind = 'r'
AND relnamespace NOT IN ('information_schema'::regnamespace, 'pg_catalog'::regnamespace)
GROUP BY relnamespace, relname, oid
ORDER BY size_mb DESC
LIMIT 100