Handy Postgres statistics queries for web development.

tables-accessed.sql

/*
 * Find which tables were involved in a number of queries.
 * This is obviously a contrived example. A practical application would be to wrap all
 * the queries for a web page in a transaction (perhaps using before/after filters in
 * the case of Rails) and then use the stats for cache invalidation.
 */

BEGIN TRANSACTION;
  /* Run the queries */
  SELECT * from news LIMIT 5;
  SELECT count(*) FROM news_trending;
  /* Now get the stats */
  SELECT * FROM pg_stat_xact_user_tables WHERE seq_scan + idx_scan + seq_tup_read > 0;
END;

tables-accessed.txt

 relid | schemaname |    relname    | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd 
-------+------------+---------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------
 18181 | public     | news_trending |        1 |         8583 |        0 |             0 |         0 |         0 |         0 |             0
 17862 | public     | news          |        1 |            5 |        0 |             0 |         0 |         0 |         0 |             0
(2 rows)