performancepostgresqlstatisticsquery-performance

pgstattuple query performance

I am trying to build query that checks dead tuples, live tuples percent, free space etc. in all my users tables using pgstattuple extension. So, I came up with this query:

SELECT schemaname, 
relname, 
(pgstattuple(schemaname ||'.'||relname)).*
FROM pg_statio_user_tables;

pg_statio_user_tables is a pg_catalog view, that uses another system view. So at first glance the query looks quite simple, but it ended up with a total execution time of 27 minutes for 882 objects in db.

The execution plan for this query: execution plan

Is there a way to rewrite query still using pgstattuple so it'll perform decent?

Solution

  • 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:

    Since Postgres 9.4 pgstattuple() can take an OID, which is preferable.