pgstattuple module/extension provides various functions to obtain tuple-level statistics, however its not installed by default.
postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;
ERROR: function pgstattuple(unknown) does not existLINE 1: ...(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattupl...HINT: No function matches the given name and argument types. You might need to add explicit type casts.
To create extension, you need to install
[root@linuxpg1 ~]# yum install postgresql14-contrib
Loaded plugins: langpacks, ulninfo
ol7_UEKR5 | 3.0 kB 00:00
ol7_latest | 3.6 kB 00:00
pgdg-common/7Server/x86_64/signature | 198 B 00:00
pgdg-common/7Server/x86_64/signature | 2.9 kB 00:00 !!!
pgdg11/7Server/x86_64/signature | 198 B 00:00
pgdg11/7Server/x86_64/signature | 3.6 kB 00:00 !!!
pgdg12/7Server/x86_64/signature | 198 B 00:00
pgdg12/7Server/x86_64/signature | 3.6 kB 00:00 !!!
pgdg13/7Server/x86_64/signature | 198 B 00:00
pgdg13/7Server/x86_64/signature | 3.6 kB 00:00 !!!
pgdg14/7Server/x86_64/signature | 198 B 00:00
pgdg14/7Server/x86_64/signature | 3.6 kB 00:00 !!!
pgdg15/7Server/x86_64/signature | 198 B 00:00
pgdg15/7Server/x86_64/signature | 3.6 kB 00:00 !!!
(1/6): pgdg13/7Server/x86_64/primary_db | 287 kB 00:03
(2/6): pgdg-common/7Server/x86_64/primary_db | 178 kB 00:03
(3/6): pgdg15/7Server/x86_64/primary_db | 106 kB 00:00
(4/6): pgdg14/7Server/x86_64/primary_db | 193 kB 00:04
(5/6): pgdg12/7Server/x86_64/primary_db | 373 kB 00:05
(6/6): pgdg11/7Server/x86_64/primary_db | 487 kB 00:11
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql14-server(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql14-libs(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql14(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64
--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: postgresql 14-contrib-14.8-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql14.x86_64 0:14.7-1PGDG.rhel7 will be updated
---> Package postgresql14.x86_64 0:14.8-1PGDG.rhel7 will be an update
---> Package postgresql14-libs.x86_64 0:14.7-1PGDG.rhel7 will be updated
---> Package postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7 will be an update
---> Package postgresql14-server.x86_64 0:14.7-1PGDG.rhel7 will be updated
---> Package postgresql14-server.x86_64 0:14.8-1PGDG.rhel7 will be an update
---> Package python3-libs.x86_64 0:3.6.8-18.0.5.el7 will be installed
--> Processing Dependency: python(abi) = 3.6 for package: python3-libs-3.6.8-18.0.5.el7.x86_64
--> Running transaction check
---> Package python3.x86_64 0:3.6.8-18.0.5.el7 will be installed
--> Processing Dependency: python3-pip for package: python3-3.6.8-18.0.5.el7.x86_64
--> Processing Dependency: python3-setuptools for package: python3-3.6.8-18.0.5.el7.x86_64
--> Running transaction check
---> Package python3-pip.noarch 0:9.0.3-8.0.3.el7 will be installed
---> Package python3-setuptools.noarch 0:39.2.0-10.0.1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql14-contrib x86_64 14.8-1PGDG.rhel7 pgdg14 688 k
Installing for dependencies:
python3 x86_64 3.6.8-18.0.5.el7 ol7_latest 70 k
python3-libs x86_64 3.6.8-18.0.5.el7 ol7_latest 6.9 M
python3-pip noarch 9.0.3-8.0.3.el7 ol7_latest 1.6 M
python3-setuptools noarch 39.2.0-10.0.1.el7 ol7_latest 628 k
Updating for dependencies:
postgresql14 x86_64 14.8-1PGDG.rhel7 pgdg14 1.5 M
postgresql14-libs x86_64 14.8-1PGDG.rhel7 pgdg14 271 k
postgresql14-server x86_64 14.8-1PGDG.rhel7 pgdg14 5.4 M
Transaction Summary
================================================================================
Install 1 Package (+4 Dependent packages)
Upgrade ( 3 Dependent packages)
Total size: 17 M
Total download size: 9.9 M
Is this ok [y/d/N]: y
Downloading packages:
(1/5): python3-3.6.8-18.0.5.el7.x86_64.rpm | 70 kB 00:01
(2/5): python3-pip-9.0.3-8.0.3.el7.noarch.rpm | 1.6 MB 00:00
(3/5): python3-setuptools-39.2.0-10.0.1.el7.noarch.rpm | 628 kB 00:00
(4/5): python3-libs-3.6.8-18.0.5.el7.x86_64.rpm | 6.9 MB 00:06
(5/5): postgresql14-contrib-14.8-1PGDG.rhel7.x86_64.rpm | 688 kB 00:08
--------------------------------------------------------------------------------
Total 1.1 MB/s | 9.9 MB 00:08
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : python3-libs-3.6.8-18.0.5.el7.x86_64 1/11
Installing : python3-pip-9.0.3-8.0.3.el7.noarch 2/11
Installing : python3-setuptools-39.2.0-10.0.1.el7.noarch 3/11
Installing : python3-3.6.8-18.0.5.el7.x86_64 4/11
Updating : postgresql14-libs-14.8-1PGDG.rhel7.x86_64 5/11
Updating : postgresql14-14.8-1PGDG.rhel7.x86_64 6/11
Updating : postgresql14-server-14.8-1PGDG.rhel7.x86_64 7/11
Installing : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64 8/11
Cleanup : postgresql14-server-14.7-1PGDG.rhel7.x86_64 9/11
Cleanup : postgresql14-14.7-1PGDG.rhel7.x86_64 10/11
Cleanup : postgresql14-libs-14.7-1PGDG.rhel7.x86_64 11/11
Verifying : python3-3.6.8-18.0.5.el7.x86_64 1/11
Verifying : postgresql14-libs-14.8-1PGDG.rhel7.x86_64 2/11
Verifying : python3-pip-9.0.3-8.0.3.el7.noarch 3/11
Verifying : python3-libs-3.6.8-18.0.5.el7.x86_64 4/11
Verifying : postgresql14-14.8-1PGDG.rhel7.x86_64 5/11
Verifying : python3-setuptools-39.2.0-10.0.1.el7.noarch 6/11
Verifying : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64 7/11
Verifying : postgresql14-server-14.8-1PGDG.rhel7.x86_64 8/11
Verifying : postgresql14-server-14.7-1PGDG.rhel7.x86_64 9/11
Verifying : postgresql14-14.7-1PGDG.rhel7.x86_64 10/11
Verifying : postgresql14-libs-14.7-1PGDG.rhel7.x86_64 11/11
Installed:
postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7
Dependency Installed:
python3.x86_64 0:3.6.8-18.0.5.el7
python3-libs.x86_64 0:3.6.8-18.0.5.el7
python3-pip.noarch 0:9.0.3-8.0.3.el7
python3-setuptools.noarch 0:39.2.0-10.0.1.el7
Dependency Updated:
postgresql14.x86_64 0:14.8-1PGDG.rhel7
postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7
postgresql14-server.x86_64 0:14.8-1PGDG.rhel7
Complete!
[root@linuxpg1 ~]# su - postgres
Last login: Sat May 27 17:22:44 CDT 2023 on pts/1
ps-bash-4.2$ psql
psql (14.8)
Type "help" for help.
postgres=# create extension pgstattuple;
CREATE EXTENSION
postgres=#
Now you can use pgstattuple to query bloat (dead_tuple_percent)
postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;
table_size | index_size | dead_tuple_percent
------------+------------+--------------------
65 MB | 21 MB | 0
(1 row)
postgres=# delete from ridx1 where x % 3 = 0;
DELETE 333333
postgres=# analyze ridx1;
ANALYZE
postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;
table_size | index_size | dead_tuple_percent
------------+------------+--------------------
65 MB | 21 MB | 29.78
(1 row)
Index bloat
postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;
table_size | index_size | bloat_ratio
------------+------------+-------------
65 MB | 21 MB | 39.86
(1 row)
postgres=# \q
-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1
INFO: Dry run enabled, not executing repack
ERROR: pg_repack failed with error: pg_repack 1.4.8 is not installed in the database
-bash-4.2$ psql
psql (14.8)
Type "help" for help.
postgres=# select * from pg_extension;oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition-------+-------------+----------+--------------+----------------+------------+-----------+--------------14473 | plpgsql | 10 | 11 | f | 1.0 | |33437 | pgstattuple | 10 | 2200 | t | 1.5 | |(2 rows)postgres=# create extension pg_repack;CREATE EXTENSIONpostgres=# select * from pg_extension;oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition-------+-------------+----------+--------------+----------------+------------+-----------+--------------14473 | plpgsql | 10 | 11 | f | 1.0 | |33437 | pgstattuple | 10 | 2200 | t | 1.5 | |33448 | pg_repack | 10 | 2200 | f | 1.4.8 | |(3 rows)postgres=# \q
-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1INFO: Dry run enabled, not executing repackWARNING: relation "public.ridx1" must have a primary key or not-null unique keys
-bash-4.2$ psqlpsql (14.8)Type "help" for help.postgres=# alter table ridx1 add constraint ridx1_pk primary key (x);ALTER TABLEpostgres=# \q
-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1INFO: Dry run enabled, not executing repackINFO: repacking table "public.ridx1"
-bash-4.2$ psqlpsql (14.8)Type "help" for help.postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;table_size | index_size | bloat_ratio------------+------------+-------------65 MB | 21 MB | 39.86(1 row)
postgres=# \q-bash-4.2$ /usr/pgsql-14/bin/pg_repack -d postgres --table ridx1INFO: repacking table "public.ridx1"
-bash-4.2$ psqlpsql (14.8)Type "help" for help.postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;table_size | index_size | bloat_ratio------------+------------+-------------------43 MB | 14 MB | 9.939999999999998(1 row)
No comments:
Post a Comment