It gets a lot faster if you run the function in a subquery and only decompose the resulting row type in the outer SELECT list:
SELECT s.schemaname, s.relname, st.*
FROM pg_statio_user_tables s
CROSS JOIN LATERAL pgstattuple(s.relid) AS st
ORDER BY 1, 2; -- optional
This way, the function is only called once per row in pg_statio_user_tables. You were stumbling over a weak spot in the Postgres query planner. See:
- How to avoid multiple function evals with the (func()).* syntax in a query?
Since Postgres 9.4 pgstattuple() can take an OID, which is preferable.
PostgreSQL
postgresql.org › docs › current › pgstattuple.html
PostgreSQL: Documentation: 18: F.33. pgstattuple — obtain tuple-level statistics
May 14, 2026 - F.33. pgstattuple — obtain tuple-level statistics # F.33.1. Functions F.33.2. Authors The pgstattuple module provides various functions to obtain tuple-level statistics. …
AWS
aws.amazon.com › blogs › database › improve-postgresql-performance-using-the-pgstattuple-extension
Improve PostgreSQL performance using the pgstattuple extension | Amazon Web Services
April 2, 2025 - In this post, we explore the pgstattuple extension in depth; what insights it offers, how to use it to diagnose issues in Amazon Aurora PostgreSQL-Compatible Edition and Amazon Relational Database Service (Amazon RDS) for PostgreSQL, and best practices for harnessing its capabilities.
Neon
neon.com › docs › extensions › pgstattuple
The pgstattuple extension - Neon Docs
The pgstattuple extension provides a suite of functions to inspect the physical storage of Postgres tables and indexes at a detailed, tuple (row) level. It offers insights into issues like table and i...
Percona
percona.com › home › blog › postgresql tuple-level statistics with pgstattuple
PostgreSQL Tuple-Level Statistics With pgstattuple - Percona
October 9, 2023 - Learn how the pgstattuple extension can be used for tuple-level analysis and finding bloated tables or vacuuming candidates.
GitHub
github.com › postgres › postgres › blob › master › contrib › pgstattuple › pgstattuple.c
postgres/contrib/pgstattuple/pgstattuple.c at master · postgres/postgres
typedef struct pgstattuple_type · { uint64 table_len; uint64 tuple_count; uint64 tuple_len; uint64 dead_tuple_count; uint64 dead_tuple_len; uint64 free_space; /* free/reusable space in bytes */ } pgstattuple_type; · typedef void (*pgstat_page) (pgstattuple_type *, Relation, BlockNumber, BufferAccessStrategy); ·
Author postgres
CSE CGI Server
cgi.cse.unsw.edu.au › ~cs9315 › 16s1 › postgresql › documentation › pgstattuple.html
pgstattuple
pgstattuple returns a relation's physical length, percentage of "dead" tuples, and other info. This may help users to determine whether vacuum is necessary or not. The argument is the target relation's name (optionally schema-qualified) or OID.
Medium
medium.com › codex › postgresql-pgstattuple-extension-analyzing-dead-tuples-efficiently-02e5f98e74a7
PostgreSQL pgstattuple Extension: Analyzing Dead Tuples Efficiently
October 3, 2025 - “pgstattuple” is a PostgreSQL extension that provides insights into table size, dead tuples, and free space.
The AWS News Feed
aws-news.com › article › 2025-04-02-improve-postgresql-performance-using-the-pgstattuple-extension
Improve PostgreSQL performance using the pgstattuple extension | The AWS News Feed
April 2, 2025 - The pgstattuple extension provides detailed PostgreSQL database storage insights, helping developers diagnose and manage table and index bloat,…
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 1474739 › pgstattuple-query-performance
pgstattuple query performance - Microsoft Q&A
January 2, 2024 - Here is the blog post provides a detailed explanation of how to use pgstattuple to get tuple-level statistics for PostgreSQL tables, including dead tuples, live tuples, free space, and more.
OpenSourceDB
opensource-db.com › home › blog › unlocking postgresql’s potential with pgstattuple
Unlocking PostgreSQL’s potential with pgstattuple - OpenSourceDB
August 21, 2024 - Introduction pgstattuple is an open-source utility in PostgreSQL, provided as an extension. It helps manage and clear table bloat, which […]
PIGSTY
pigsty.io › ext › e › pgstattuple
pgstattuple | PIGSTY
March 12, 2026 - pgstattuple provides functions to obtain tuple-level statistics for tables and indexes, including dead tuple counts and free space metrics.
DEV Community
dev.to › hujan › configure-pgstattuple-3pnf
Configure pgstattuple - DEV Community
August 14, 2025 - pgstattuple is part of PostgreSQL's contrib modules—a collection of officially supported but optional extensions.
PostgreSQL
postgresql.org › message-id › E1jq2xV-0003F0-Io@gemulon.postgresql.org
PostgreSQL: pgsql: pgstattuple: Have pgstattuple_approx accept TOAST tables
June 29, 2020 - Add test cases to show how various pgstattuple functions accept TOAST tables.
PostgreSQL
postgresql.org › docs › 8.4 › pgstattuple.html
PostgreSQL: Documentation: 8.4: pgstattuple
July 24, 2014 - pgstattuple returns a relation's physical length, percentage of "dead" tuples, and other info. This may help users to determine whether vacuum is necessary or not. The argument is the target relation's name (optionally schema-qualified).
Packtpub
subscription.packtpub.com › book › data › 9781783555314 › 7 › ch07lvl1sec44 › inspecting-internal-information
7. PostgreSQL Monitoring | Troubleshooting PostgreSQL
The pgstattuple module has been created to detect those problems.
Alexandrubagu
alexandrubagu.github.io › blog › postgresql-monitoring-extensions.html
Essential PostgreSQL Monitoring Extensions: pg_buffercache, pg_stat_statements, pgstattuple, pg_prewarm - Bâgu Alexandru Bogdan
January 30, 2026 - These four PostgreSQL extensions provide essential visibility into database performance. pg_buffercache reveals memory usage, pg_stat_statements tracks query performance, pgstattuple detects bloat, and pg_prewarm ensures fast restarts.
Enteros, Inc
enteros.com › главная страница › enteros’ blog – thoughts on database technology, machine / deep learning, and a generative ai › table bloat estimation in postgresql
TABLE BLOAT ESTIMATION IN POSTGRESQL
December 29, 2022 - Both methods of estimating are very cheap and involve making some educated guesses based on statistics. The “pgstattuple” extension also has a pgstattuple() method, which we will include in our comparisons to get accurate information on bloat, but this method may be expensive because it does a full table scan.
EnterpriseDB
enterprisedb.com › docs › tpa › latest › reference › postgres_extension_configuration
EDB Docs - Trusted Postgres Architect v23.43.0 - Adding Postgres extensions
pgstattuple · Additional extensions can be configured within config.yml, by specifying the extension name, any required shared preload entries and the package containing the extension.
Postgresql
doxygen.postgresql.org › pgstatindex_8c.html
PostgreSQL Source Code: contrib/pgstattuple/pgstatindex.c File Reference
422 errmsg("must be superuser to use pgstattuple functions"))); 423 · 424 relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); 425 rel = relation_openrv(relrv, AccessShareLock); 426 · 427 PG_RETURN_INT64(pg_relpages_impl(rel)); 428} errcode ·