I am trying to understand the impact of deletions to bloat of tables and indexes after large number of inserts and deletes. The inserts and deletes are following a rather strict pattern: first the records are sequentially inserted, rarelly (or pretty much never) updated in the next period of time (usually five years), and deleted once this threshold (five years) is exceeded. This is a typical scenario in many enterprise software systems, where one needs to keep the records for compliance reasons.
The database (PostgreSQL 9.6.5) runs with a pretty standart configuration (with some parameters incresed for fast query and maintenance processing).
I am trying to simulate and analyze the bloat of the tables and (typical) indexes. On the conceptual level, (1) create a table, (2) analyze it (3) insert records (4) analyze it again, (5) delete half of the records (6) analyze it (7) analyze it again, (8) insert the number of deleted records again (9) analyze it and (10) analyze it again and (11) check the indexes and tables for bloat.
The complete SQL code is as follows:
set LC_MESSAGES ='C'
create extension "uuid-ossp"
drop table v1;
create table v1 (
id serial primary key,
id_uuid_v1 uuid default uuid_generate_v1(),
id_uuid_v4 uuid default uuid_generate_v4(),
t timestamp with time zone default clock_timestamp(),
name varchar
);
create index ix_v1_uuid on v1 (id_uuid_v1);
create index ix_v4_uuid on v1 (id_uuid_v4);
create index ix_v1_t on v1 (t);
vacuum (verbose, analyze, freeze) v1;
select pg_size_pretty(pg_relation_size('v1')), count(*)
from v1;
-- emtpy the table
-- truncate v1;
-- generate 100K records
INSERT INTO v1 (name)
SELECT x.id
FROM generate_series(1,100000) AS x(id);
vacuum (verbose, analyze, freeze) v1;
vacuum (verbose, analyze, freeze) v1;
-- delete half of the records
delete from v1
where id <= (select avg(id) from v1);
-- free the pages
vacuum (verbose, analyze, freeze) v1;
vacuum (verbose, analyze, freeze) v1;
--vacuum full v1;
-- generate the number another half of the records
with c as (
select count(*) c
from v1
)
INSERT INTO v1 (name)
SELECT generate_series(0,c.c-1)
from c;
-- updated statistics
vacuum (verbose, analyze, freeze) v1;
select *
from v1
limit 10
the bloat reported after this is:
For the table
schemaname | tblname | bloat_ratio | bloat_size | bloat_size_pretty | real_size | real_size_pretty | ?column?
------------+---------+-------------+------------+-------------------+-----------+------------------+------------------------
public | v1 | 0.2 | 16384 | 16 kB | 8445952 | 8248 kB | VACUUM FULL public.v1;
for the indexes:
schemaname | tblname | idxname | bloat_ratio | bloat_size | bloat_size_pretty | real_size | real_size_pretty | ?column?
------------+---------+------------+-------------+------------+-------------------+-----------+------------------+----------------------------------
public | v1 | ix_v1_uuid | 31.9 | 1466368 | 1432 kB | 4603904 | 4496 kB | REINDEX INDEX public.ix_v1_uuid;
public | v1 | ix_v4_uuid | 29.7 | 1327104 | 1296 kB | 4464640 | 4360 kB | REINDEX INDEX public.ix_v4_uuid;
public | v1 | ix_v1_t | 0.4 | 8192 | 8192 bytes | 2260992 | 2208 kB | REINDEX INDEX public.ix_v1_t;
public | v1 | v1_pkey | 0.4 | 8192 | 8192 bytes | 2260992 | 2208 kB | REINDEX INDEX public.v1_pkey;
My expectations were:
- serial btree would not bloat (confirmed)
- temporal btree would not bloat (confirmed)
- V1 UUID btree would not bloat (oposite what expected confirmed)
- V4 UUID btree would bloat (confirmed)
- V4 bloat is larger than V1 bloat (opposite what expected confirmed)
UPDATE (reasons why for the expectations):
- serial Btree would not bloat with sequential deletions, since the pages in the btree would delete sequentially: it is not just a few records from the page that are deleted, but a complete page is freed. This page is later reused as inserts are getting in.
- similar argument follows the temporal btree: temporal values in b-tree are stored as floats. Those floats are deleted sequentially, the complete page is freed, marked as freed by vacuum, and later reused by the insert.
- V1 UUID btree is actually 128 bits that follow the structure: [time_now][hash of mac adress] (check the table bellow). Deletions would need to be also sequential, a complete block needs to be freed (and actually some of it is possible to see from the vacuum verbose). V1 Index should actually behave similar to temporal index (which works very nicely, i.e., does not bloat).
- V4 UUID is a random 128 ID. Deletions of half of the record would make the pages look like a sieve. After deletions all pages would contain half of the records empty, i.e., bloat.
- reinsertion into the bloated V4 index reduces the bloating (last point in the expectation list). This is somewhat understandable, but since V1 should not bloat, V1 index must perform way better.
What is going on??!
Additional infos from vacuum verboze analyze:
Before anything
INFO: vacuuming "public.v1"
INFO: index "v1_pkey" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_uuid" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v4_uuid" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_t" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "v1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_1701065"
INFO: index "pg_toast_1701065_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1701065": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.v1"
INFO: "v1": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
Query returned successfully with no result in 37 msec.
After Insert
INFO: vacuuming "public.v1"
INFO: index "v1_pkey" now contains 100000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_uuid" now contains 100000 row versions in 388 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v4_uuid" now contains 100000 row versions in 534 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_t" now contains 100000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "v1": found 0 removable, 100000 nonremovable row versions in 1031 out of 1031 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO: vacuuming "pg_toast.pg_toast_1701082"
INFO: index "pg_toast_1701082_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1701082": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.v1"
INFO: "v1": scanned 1031 of 1031 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
Query returned successfully with no result in 179 msec.
After deleted, 1st run of vacuum
INFO: vacuuming "public.v1"
INFO: scanned index "v1_pkey" to remove 50000 row versions
DETAIL: CPU 0.00s/0.01u sec elapsed 0.00 sec
INFO: scanned index "ix_v1_uuid" to remove 50000 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec
INFO: scanned index "ix_v4_uuid" to remove 50000 row versions
DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec
INFO: scanned index "ix_v1_t" to remove 50000 row versions
DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec
INFO: "v1": removed 50000 row versions in 516 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec
INFO: index "v1_pkey" now contains 50000 row versions in 276 pages
DETAIL: 50000 index row versions were removed.
136 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_uuid" now contains 50000 row versions in 388 pages
DETAIL: 50000 index row versions were removed.
191 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v4_uuid" now contains 50000 row versions in 534 pages
DETAIL: 50000 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_t" now contains 50000 row versions in 276 pages
DETAIL: 50000 index row versions were removed.
136 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "v1": found 50000 removable, 142 nonremovable row versions in 517 out of 1031 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.04u sec elapsed 0.05 sec.
INFO: vacuuming "pg_toast.pg_toast_1701082"
INFO: index "pg_toast_1701082_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1701082": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.v1"
INFO: "v1": scanned 1031 of 1031 pages, containing 50000 live rows and 0 dead rows; 30000 rows in sample, 50000 estimated total rows
Query returned successfully with no result in 116 msec.
2nd vacuum
INFO: vacuuming "public.v1"
INFO: index "v1_pkey" now contains 50000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
136 index pages have been deleted, 136 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_uuid" now contains 50000 row versions in 388 pages
DETAIL: 0 index row versions were removed.
191 index pages have been deleted, 191 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v4_uuid" now contains 50000 row versions in 534 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_t" now contains 50000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
136 index pages have been deleted, 136 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "v1": found 0 removable, 90 nonremovable row versions in 1 out of 1031 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: vacuuming "pg_toast.pg_toast_1701082"
INFO: index "pg_toast_1701082_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1701082": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.v1"
INFO: "v1": scanned 1031 of 1031 pages, containing 50000 live rows and 0 dead rows; 30000 rows in sample, 50000 estimated total rows
Query returned successfully with no result in 272 msec.
After insert again, 1st vacuum
INFO: vacuuming "public.v1"
INFO: index "v1_pkey" now contains 100000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_uuid" now contains 100000 row versions in 562 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v4_uuid" now contains 100000 row versions in 545 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_t" now contains 100000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "v1": found 0 removable, 50142 nonremovable row versions in 517 out of 1031 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_1701082"
INFO: index "pg_toast_1701082_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1701082": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.v1"
INFO: "v1": scanned 1031 of 1031 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
Query returned successfully with no result in 180 msec.
2nd vacuum
INFO: vacuuming "public.v1"
INFO: index "v1_pkey" now contains 100000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_uuid" now contains 100000 row versions in 562 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v4_uuid" now contains 100000 row versions in 545 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ix_v1_t" now contains 100000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "v1": found 0 removable, 90 nonremovable row versions in 1 out of 1031 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_1701082"
INFO: index "pg_toast_1701082_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1701082": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.v1"
INFO: "v1": scanned 1031 of 1031 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
Query returned successfully with no result in 158 msec.
Sample data
id | id_uuid_v1 | id_uuid_v4 | t | name
--------+--------------------------------------+--------------------------------------+-------------------------------+------
100001 | 21515a0a-f07c-11e7-9a90-509a4c44f517 | 18632922-249f-439f-9b54-6cb29f8c97a2 | 2018-01-03 12:49:18.368387+01 | 0
100002 | 2152bad0-f07c-11e7-9b55-509a4c44f517 | 0ba51425-7225-416f-a79f-b2db0ed23155 | 2018-01-03 12:49:18.377475+01 | 1
100003 | 2152bad1-f07c-11e7-a7a0-509a4c44f517 | 486fb3ae-204f-4c8a-8171-7089b2c9d946 | 2018-01-03 12:49:18.377699+01 | 2
100004 | 2152ce26-f07c-11e7-8172-509a4c44f517 | 83ec70ea-69bd-45a7-800c-eac558521e19 | 2018-01-03 12:49:18.3779+01 | 3
100005 | 2152ce27-f07c-11e7-800d-509a4c44f517 | cc5f356f-589d-4bcd-b627-b731e496c56d | 2018-01-03 12:49:18.378173+01 | 4
100006 | 2152e1b8-f07c-11e7-b628-509a4c44f517 | 495e4216-76ca-4806-90d8-d0cc1d8ea53f | 2018-01-03 12:49:18.378373+01 | 5
100007 | 2152e1b9-f07c-11e7-90d9-509a4c44f517 | aaa824bf-34e1-4ec9-a589-b86ad77d9df0 | 2018-01-03 12:49:18.37857+01 | 6
100008 | 2152f54a-f07c-11e7-a58a-509a4c44f517 | 0474fd4a-6f2c-4717-a59d-d6c6337eabee | 2018-01-03 12:49:18.378765+01 | 7
100009 | 2152f54b-f07c-11e7-a59e-509a4c44f517 | 5f416afa-7a4f-4692-bbe1-991a99cc8e25 | 2018-01-03 12:49:18.378958+01 | 8
100010 | 2152f54c-f07c-11e7-bbe2-509a4c44f517 | f32e5a17-40b1-4ab0-af41-a3557ff4fb3e | 2018-01-03 12:49:18.379249+01 | 9
Code to check indexes:
CREATE OR REPLACE VIEW public.bloat_inx AS
SELECT o.schemaname::text AS schemaname,
o.tblname::text AS tblname,
o.idxname::text AS idxname,
round(o.bloat_ratio::numeric, 1) AS bloat_ratio,
o.bloat_size::numeric AS bloat_size,
pg_size_pretty(o.bloat_size::bigint) AS bloat_size_pretty,
o.relpages * 8 * 1024::bigint AS real_size,
pg_size_pretty(o.relpages * 8 * 1024::bigint) AS real_size_pretty,
((('REINDEX INDEX '::text || o.schemaname::text) || '.'::text) || o.idxname::text) || ';'::text
FROM ( SELECT current_database() AS current_database,
sub.nspname AS schemaname,
sub.tblname,
sub.idxname,
sub.bs * (sub.relpages::double precision - sub.est_pages)::bigint::numeric AS extra_size,
100::double precision * (sub.relpages::double precision - sub.est_pages) / sub.relpages::double precision AS extra_ratio,
sub.fillfactor,
sub.bs::double precision * (sub.relpages::double precision - sub.est_pages_ff) AS bloat_size,
100::double precision * (sub.relpages::double precision - sub.est_pages_ff) / sub.relpages::double precision AS bloat_ratio,
sub.relpages,
sub.is_na
FROM ( SELECT COALESCE(1::double precision + ceil(s2.reltuples / floor((s2.bs - s2.pageopqdata::numeric - s2.pagehdr::numeric)::double precision / (4::numeric + s2.nulldatahdrwidth)::double precision)), 0::double precision) AS est_pages,
COALESCE(1::double precision + ceil(s2.reltuples / floor(((s2.bs - s2.pageopqdata::numeric - s2.pagehdr::numeric) * s2.fillfactor::numeric)::double precision / (100::double precision * (4::numeric + s2.nulldatahdrwidth)::double precision))), 0::double precision) AS est_pages_ff,
s2.bs,
s2.nspname,
s2.table_oid,
s2.tblname,
s2.idxname,
s2.relpages,
s2.fillfactor,
s2.is_na
FROM ( SELECT s1.maxalign,
s1.bs,
s1.nspname,
s1.tblname,
s1.idxname,
s1.reltuples,
s1.relpages,
s1.relam,
s1.table_oid,
s1.fillfactor,
((s1.index_tuple_hdr_bm + s1.maxalign -
CASE
WHEN (s1.index_tuple_hdr_bm % s1.maxalign) = 0 THEN s1.maxalign
ELSE s1.index_tuple_hdr_bm % s1.maxalign
END)::double precision + s1.nulldatawidth + s1.maxalign::double precision -
CASE
WHEN s1.nulldatawidth = 0::double precision THEN 0
WHEN (s1.nulldatawidth::integer % s1.maxalign) = 0 THEN s1.maxalign
ELSE s1.nulldatawidth::integer % s1.maxalign
END::double precision)::numeric AS nulldatahdrwidth,
s1.pagehdr,
s1.pageopqdata,
s1.is_na
FROM ( SELECT i.nspname,
i.tblname,
i.idxname,
i.reltuples,
i.relpages,
i.relam,
a.attrelid AS table_oid,
current_setting('block_size'::text)::numeric AS bs,
i.fillfactor,
CASE
WHEN version() ~ 'mingw32'::text OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64'::text THEN 8
ELSE 4
END AS maxalign,
24 AS pagehdr,
16 AS pageopqdata,
CASE
WHEN max(COALESCE(s.null_frac, 0::real)) = 0::double precision THEN 2
ELSE 2 + (32 + 8 - 1) / 8
END AS index_tuple_hdr_bm,
sum((1::double precision - COALESCE(s.null_frac, 0::real)) * COALESCE(s.avg_width, 1024)::double precision) AS nulldatawidth,
max(
CASE
WHEN a.atttypid = 'name'::regtype::oid THEN 1
ELSE 0
END) > 0 AS is_na
FROM pg_attribute a
JOIN ( SELECT pg_namespace.nspname,
tbl.relname AS tblname,
idx.relname AS idxname,
idx.reltuples,
idx.relpages,
idx.relam,
pg_index.indrelid,
pg_index.indexrelid,
pg_index.indkey::smallint[] AS attnum,
COALESCE("substring"(array_to_string(idx.reloptions, ' '::text), 'fillfactor=([0-9]+)'::text)::smallint::integer, 90) AS fillfactor
FROM pg_index
JOIN pg_class idx ON idx.oid = pg_index.indexrelid
JOIN pg_class tbl ON tbl.oid = pg_index.indrelid
JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
WHERE pg_index.indisvalid AND tbl.relkind = 'r'::"char" AND idx.relpages > 0) i ON a.attrelid = i.indexrelid
JOIN pg_stats s ON s.schemaname = i.nspname AND (s.tablename = i.tblname AND s.attname::text = pg_get_indexdef(a.attrelid, a.attnum::integer, true) OR s.tablename = i.idxname AND s.attname = a.attname)
JOIN pg_type t ON a.atttypid = t.oid
WHERE a.attnum > 0
GROUP BY i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid, (current_setting('block_size'::text)::numeric), i.fillfactor) s1) s2
JOIN pg_am am ON s2.relam = am.oid
WHERE am.amname = 'btree'::name) sub
WHERE NOT sub.is_na) o
WHERE o.schemaname = 'public'::name
ORDER BY (o.bloat_size::numeric) DESC;
UPDATE 2: of course, if one replaces vacuum analyze with vacuum full, the problem is solved. But vacuum full locks the table (for write), which is very undesirable in an operative environment.
