To: pgsql-performance@lists.postgresql.org
References: 
 <CADu1mRN1PPXO0p2HUF4NkrLSLmdiXXcafm5B6r2kk7Tawd1UoQ@mail.gmail.com>
From: Gilles Darold <gilles@darold.net>
Subject: Re: Very slow "bloat query"
Message-ID: <804ea9ef-e9d4-e104-a9f1-d9a94e36013c@darold.net>
Date: Fri, 14 May 2021 13:50:54 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.8.1
MIME-Version: 1.0
In-Reply-To: 
 <CADu1mRN1PPXO0p2HUF4NkrLSLmdiXXcafm5B6r2kk7Tawd1UoQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Language: fr
Archived-At: 
 <https://www.postgresql.org/message-id/804ea9ef-e9d4-e104-a9f1-d9a94e36013c%40darold.net>
Precedence: bulk

Le 14/05/2021 =C3=A0 13:06, Marcin Gozdalik a =C3=A9crit=C2=A0:
> Hi
>
> I am trying to use `pgmetrics` on a big (10TB+), busy (1GB/s RW)=20
> database. It takes around 5 minutes for pgmetrics to run. I traced the =

> problem to the "bloat query" (version of=20
> https://wiki.postgresql.org/wiki/Show_database_bloat=20
> <https://wiki.postgresql.org/wiki/Show_database_bloat>) spinning in=20
> CPU, doing no I/O.
>
> I have traced the problem to the bloated `pg_class` (the irony:=20
> `pgmetrics` does not collect bloat on `pg_catalog`):
> `vacuum (full, analyze, verbose) pg_class;`
> ```
> INFO: =C2=A0vacuuming "pg_catalog.pg_class"
> INFO: =C2=A0"pg_class": found 1 removable, 7430805 nonremovable row=20
> versions in 158870 pages
> DETAIL: =C2=A07429943 dead row versions cannot be removed yet.
> CPU 1.36s/6.40u sec elapsed 9.85 sec.
> INFO: =C2=A0analyzing "pg_catalog.pg_class"
> INFO: =C2=A0"pg_class": scanned 60000 of 158869 pages, containing 295 l=
ive=20
> rows and 2806547 dead rows; 295 rows in sample, 781 estimated total row=
s
> VACUUM
> ```
>
> `pg_class` has so many dead rows because the workload is temp-table=20
> heavy (creating/destroying 1M+ temporary tables per day) and has long=20
> running analytics queries running for 24h+.
>
> PG query planner assumes that index scan on `pg_class` will be very=20
> quick and plans Nested loop with Index scan. However, the index scan=20
> has 7M dead tuples to filter out and the query takes more than 200=20
> seconds (https://explain.depesz.com/s/bw2G=20
> <https://explain.depesz.com/s/bw2G>).
>
> If I create a temp table from `pg_class` to contain only the live tuple=
s:
> ```
> CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
> CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
> CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON=20
> pg_class_alive(relname, relnamespace);
> CREATE INDEX pg_class_tblspc_relfilenode_index ON=20
> pg_class_alive(reltablespace, relfilenode);
> ANALYZE pg_class_alive;
> ```
>
> and run the bloat query on `pg_class_alive` instead of `pg_class`:
> ```
> SELECT
> =C2=A0 =C2=A0nn.nspname AS schemaname,
> =C2=A0 =C2=A0cc.relname AS tablename,
> =C2=A0 =C2=A0COALESCE(cc.reltuples,0) AS reltuples,
> =C2=A0 =C2=A0COALESCE(cc.relpages,0) AS relpages,
> =C2=A0 =C2=A0COALESCE(CEIL((cc.reltuples*((datahdr+8-
> =C2=A0 =C2=A0 =C2=A0(CASE WHEN datahdr%8=3D0 THEN 8 ELSE datahdr%8=20
> END))+nullhdr2+4))/(8192-20::float)),0) AS otta
> =C2=A0FROM
> =C2=A0 =C2=A0 pg_class_alive cc
> =C2=A0JOIN pg_namespace nn ON cc.relnamespace =3D nn.oid AND nn.nspname=
 <>=20
> 'information_schema'
> =C2=A0LEFT JOIN
> =C2=A0(
> =C2=A0 =C2=A0SELECT
> =C2=A0 =C2=A0 =C2=A0foo.nspname,foo.relname,
> =C2=A0 =C2=A0 =C2=A0(datawidth+32)::numeric AS datahdr,
> =C2=A0 =C2=A0 =C2=A0(maxfracsum*(nullhdr+8-(case when nullhdr%8=3D0 THE=
N 8 ELSE=20
> nullhdr%8 END))) AS nullhdr2
> =C2=A0 =C2=A0FROM (
> =C2=A0 =C2=A0 =C2=A0SELECT
> =C2=A0 =C2=A0 =C2=A0 =C2=A0ns.nspname, tbl.relname,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0SUM((1-coalesce(null_frac,0))*coalesce(avg_w=
idth, 2048)) AS=20
> datawidth,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0MAX(coalesce(null_frac,0)) AS maxfracsum,
> =C2=A0 =C2=A0 =C2=A0 =C2=A023+(
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT 1+count(*)/8
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM pg_stats s2
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE null_frac<>0 AND s2.schemaname =
=3D ns.nspname AND=20
> s2.tablename =3D tbl.relname
> =C2=A0 =C2=A0 =C2=A0 =C2=A0) AS nullhdr
> =C2=A0 =C2=A0 =C2=A0FROM pg_attribute att
> =C2=A0 =C2=A0 =C2=A0JOIN pg_class_alive tbl ON att.attrelid =3D tbl.oid=

> =C2=A0 =C2=A0 =C2=A0JOIN pg_namespace ns ON ns.oid =3D tbl.relnamespace=

> =C2=A0 =C2=A0 =C2=A0LEFT JOIN pg_stats s ON s.schemaname=3Dns.nspname
> =C2=A0 =C2=A0 =C2=A0AND s.tablename =3D tbl.relname
> =C2=A0 =C2=A0 =C2=A0AND s.inherited=3Dfalse
> =C2=A0 =C2=A0 =C2=A0AND s.attname=3Datt.attname
> =C2=A0 =C2=A0 =C2=A0WHERE att.attnum > 0 AND tbl.relkind=3D'r'
> =C2=A0 =C2=A0 =C2=A0GROUP BY 1,2
> =C2=A0 =C2=A0) AS foo
> =C2=A0) AS rs
> =C2=A0ON cc.relname =3D rs.relname AND nn.nspname =3D rs.nspname
> =C2=A0LEFT JOIN pg_index i ON indrelid =3D cc.oid
> =C2=A0LEFT JOIN pg_class_alive c2 ON c2.oid =3D i.indexrelid
> ```
>
> it runs in 10s, 20x faster (https://explain.depesz.com/s/K4SH=20
> <https://explain.depesz.com/s/K4SH>)
>
> The rabbit hole probably goes deeper (e.g. should do the same for=20
> pg_statistic and pg_attribute and create a new pg_stats view).
>
> I am not able (at least not quickly) change the amount of temporary=20
> tables created or make the analytics queries finish quicker. Apart=20
> from the above hack of filtering out live tuples to a separate table=20
> is there anything I could do?


Hi,


To avoid bloating your catalog with temporary tables you can try using=20
https://github.com/darold/pgtt-rsl I don't know if it will fit the=20
performances but at least you will not bloat the catalog anymore.


About your hack, I don't see other solution except running vacuum on the =

catalog tables more often, but I guess that this is already done or not=20
possible. But not bloating the catalog at=C2=A0 such level is the right s=
olution.


--=20
Gilles Darold
http://www.darold.net/