Collect more detailed postgres metrics for specified relations

[miketheman]

We already collect some metrics with tables and index data, such as rows and counts, but not disk space-related details for given relations.

(All metrics should be tagged with the relation that they came from, so an index stat should be tagged with the relation name. This may already be the case.)

Let's start with the easy ones.

table/relation/index size totals

SELECT pg_total_relation_size('mytablename');
 pg_total_relation_size
------------------------
           335027789824
(1 row)

SELECT pg_relation_size('mytablename');
 pg_relation_size
------------------
     147676241920
(1 row)

SELECT pg_indexes_size('mytablename');
 pg_indexes_size
-----------------
    187308597248
(1 row)

Note: pg_indexes_size() was introduced in PG 9.0, so if we're still supporting 8.4 this particular one won't work.

We could wrap each result in pg_size_pretty() if we wanted to handle units transformation, like so:

SELECT pg_size_pretty(pg_total_relation_size('mytablename'));
 pg_size_pretty
----------------
 312 GB
(1 row)

(This may prove harder to parse out the actual results as they change)

per-index size from a given relation

This is a more complex query right now, and could possibly be simplified, I hadn't looked too hard into where I could reduce the joins just yet.

SELECT
    indexname,
    pg_relation_size(quote_ident(indexrelname)::text) AS index_size
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, indexrelname FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.tablename = 'mytablename';
                indexname                 |  index_size
------------------------------------------+--------------
 mytablename_pkey                         |   5052571648
 unique_context_056                       | 105983655936
 mytablename_full_idx                     |  15670239232
 mytablename_host_name_source_type_id_idx |  11991097344
 mytablename_org_id_source_type_id_idx    |   6163087360
 mytablename_source_type_id_idx           |   6494412800
 mytablename_tags_idx                     |  36338016256
(7 rows)

Descriptions for each of the functions used are in Postgres Docs.

[remh]

Nice description!

Let's make sure to use EXPLAIN to see how complex these queries are.

[alq666]

SELECT  t.tablename, psai.indexrelname, pg_relation_size(quote_ident(indexrelname)::text) AS index_size
  FROM pg_tables t
  JOIN pg_class c ON t.tablename = c.relname
  JOIN pg_index x ON c.oid = x.indrelid
  JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid
 WHERE t.tablename = 'my_table';

or more synthetically

SELECT
  nspname,
  relname,
  relkind as "type",
  pg_table_size(C.oid) AS size,
  pg_indexes_size(C.oid) AS idxsize,
  pg_total_relation_size(C.oid) as "total"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND
  nspname !~ '^pg_toast' AND
  relkind IN ('r','i');

[miketheman]

First example returns t.tablename which we don't need, since we are passing in a known table name.

Second example uses a more liberal WHERE clause, getting all details beyond the specified relations.

Leaning towards the simpler approach of collecting the desired index stats over global collection (for now) as this is an opt-in approach, reducing the probability of collecting unbounded lists.

[alq666]

One query to get index, table and total size for a subset of the tables,
preferred several queries to get the same data. Agreed on the positive
filter to apply to the query, we can simply add a relname in ...
On Jun 28, 2014 11:56 PM, "Mike Fiedler" [email protected] wrote:

First example returns t.tablename which we don't need, since we are
passing in a known table name.

Second example uses a more liberal WHERE clause, getting all details
beyond the specified relations.

Leaning towards the simpler approach of collecting the desired index stats
over global collection (for now) as this is an opt-in approach, reducing
the probability of collecting unbounded lists.

—
Reply to this email directly or view it on GitHub
#1007 (comment).