View shared buffers content

pg_shared_buffers.sql

-- based on https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c2_03_shared_buffers_current_database_detailed.sql

CREATE VIEW pg_shared_buffers AS (
  WITH stats AS (
      SELECT
        c.relname::text as name,
        pg_relation_size(c.oid)                  AS relation_size,
        count(b.bufferid) * (
          SELECT current_setting('block_size') :: INT
        )                                        AS buffered_in_shared_buffers,
        100 * count(b.bufferid) * (
          SELECT current_setting('block_size') :: INT
        ) / greatest(1, pg_relation_size(c.oid)) AS pct_of_relation,
        (100 * count(b.bufferid) / greatest(1, (
          SELECT setting
          FROM pg_settings
          WHERE name = 'shared_buffers'
        ) :: DECIMAL))                           AS pct_of_shared_buffers
      FROM pg_class c
        INNER JOIN pg_buffercache b
          ON
            b.relfilenode = c.relfilenode
            AND b.reldatabase IN (0, (
              SELECT oid
              FROM pg_database
              WHERE datname = current_database()
            ))
      GROUP BY c.oid, c.relname
      ORDER BY count(b.bufferid) * 8192 DESC
  ), gte_1_percent AS (
      SELECT
        name,
        pg_size_pretty(relation_size)              AS relation_size,
        pg_size_pretty(buffered_in_shared_buffers) AS buffered_in_shared_buffers,
        round(pct_of_relation, 1)                  AS pct_of_relation,
        round(pct_of_shared_buffers, 2)            AS pct_of_shared_buffers
      FROM stats
      WHERE pct_of_shared_buffers >= 1
      ORDER BY 5 DESC
  ), lt_1_percent AS (
      SELECT
        '<1%'::text as name,
        pg_size_pretty(sum(relation_size))              AS relation_size,
        pg_size_pretty(sum(buffered_in_shared_buffers)) AS buffered_in_shared_buffers,
        round(avg(pct_of_relation), 1)                  AS pct_of_relation,
        round(sum(pct_of_shared_buffers), 2)            AS pct_of_shared_buffers
      FROM stats
      WHERE pct_of_shared_buffers < 1
  )
  SELECT *
  FROM gte_1_percent
  UNION ALL
  SELECT *
  FROM lt_1_percent
);