#!/bin/bash

#
# bufcache.sh
#
# Shell script to execute sample queries used by the
# "Inside the PostgreSQL Shared Buffer Cache"
# presentation
#
# V1.0	3/29/2008	Greg Smith
#
# See http://www.westnet.com/~gsmith/content/postgresql for latest
# version of this file and the presentation
#

# Print basic configuration information
psql -d pgbench -c "select setting as shared_buffers,pg_size_pretty(8192 * setting::integer) as size from pg_settings where name='shared_buffers'"

# Run a benchmark to put some data in the buffer cache
pgbench -S -c 8 -t 10000 pgbench

# Top 10, from the pg_buffercache README:
psql -d pgbench -c "SELECT c.relname,count(*) AS buffers FROM pg class c INNER JOIN pg buffercache b ON b.relfilenode=c.relfilenode INNER JOIN pg database d ON (b.reldatabase=d.oid AND d.datname=current database()) GROUP BY c.relname ORDER BY 2 DESC LIMIT 10"

# Simple breakdown by usage count
psql -d pgbench -c "select usagecount,count(*),isdirty from pg_buffercache group by isdirty,usagecount order by isdirty,usagecount"

# Buffer contents summary, with percentages
psql -d pgbench -c "SELECT c.relname, pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) / (select setting from pg_settings where name='shared_buffers')::integer,1) AS buffers_percent, round(100.0 * count(*) * 8192 / pg_relation_size(c.relname),1) as percent_of_relation FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.relname ORDER BY 3 DESC LIMIT 10"

# Buffer usage count distribution
psql -d pgbench -c "SELECT c.relname, count(*) AS buffers,usagecount FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.relname,usagecount ORDER BY c.relname,usagecount"