The following bug has been logged on the website:
Bug reference: 15950
Logged by: Daniel Adeniji
Email address: daniel_adeniji@hotmail.com
PostgreSQL version: 11.4
Operating system: Windows 10
Description:
Issued the query pasted below :-
===============================
with cteSetting
(
name
, setting
)
as
(
select
name
, setting::integer
from pg_catalog.pg_settings
where name = 'block_size'
)
SELECT
tblN.nspname
as "schema"
, tblC.relname
, avg(tblC.relpages)::int
as relpages
, avg(tblC.reltuples)::int
as reltuples
, count(*) as cnt
, pg_size_pretty
(
cast
(
avg(tblPGFS.avail) as bigint
)
)
as "Av. freespace size"
/*
max(cteSBS.setting)
as "blockSize"
*/
, round
(
100
* avg
(
tblPGFS.avail
)
/
(
avg(cteSBS.setting)::int
)
,2
) as "Av. freespace ratio"
FROM pg_catalog.pg_class tblC
join pg_catalog.pg_namespace tblN
on tblC.relnamespace = tblN.oid
inner join pg_freespace(tblC.oid) tblPGFS
on TRUE
inner join cteSetting cteSBS
on cteSBS.name = 'block_size'
where tblC.relkind = 'r'
and tblN.nspname not in
(
'information_schema'
, 'pg_catalog'
)
GROUP BY
tblN.nspname
, tblC.relname
ORDER BY
tblN.nspname
, tblC.relname
;
Result :-
=========
1) pg_freespace.avail is 0