Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL Common Utility Queries
/* How to calculate postgreSQL database size in disk ? */
SELECT pg_size_pretty(pg_database_size('thedbname'));
/* Calculate size of a table including or excluding the index */
SELECT pg_size_pretty(pg_total_relation_size('big_table'));
SELECT pg_size_pretty(pg_relation_size('big_table')); /* without index */
/* See indexes on a table with `\d tablename` */
/* Finding the total size of your biggest tables */
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
/* Finding the total size of your biggest tables */
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
/* Table & index sizes along which indexes are being scanned and how many tuples are fetched */
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(t.tablename::text)) AS table_size,
pg_size_pretty(pg_relation_size(indexrelname::text)) AS index_size,
CASE WHEN x.is_unique = 1 THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
(SELECT indrelid,
max(CAST(indisunique AS integer)) AS is_unique
FROM pg_index
GROUP BY indrelid) x
ON c.oid = x.indrelid
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,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.schemaname='public'
ORDER BY 1,2;
/* Pull the number of rows, indexes, and some info about those indexes for each table */
SELECT
pg_class.relname,
pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
pg_class.reltuples AS num_rows,
count(indexname) AS number_of_indexes,
CASE WHEN x.is_unique = 1 THEN 'Y'
ELSE 'N'
END AS UNIQUE,
SUM(case WHEN number_of_columns = 1 THEN 1
ELSE 0
END) AS single_column,
SUM(case WHEN number_of_columns IS NULL THEN 0
WHEN number_of_columns = 1 THEN 0
ELSE 1
END) AS multi_column
FROM pg_namespace
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
(SELECT indrelid,
max(CAST(indisunique AS integer)) AS is_unique
FROM pg_index
GROUP BY indrelid) x
ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid )
AS foo
ON pg_class.relname = foo.ctablename
WHERE
pg_namespace.nspname='public'
AND pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;
/* Finds indices that have not been used (the ones with 0 scans) */
SELECT
schemaname,
relname,
indexrelname,
idx_scan,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE
indisunique IS false
ORDER BY idx_scan,relname;
@nesquena

This comment has been minimized.

Copy link
Owner Author

nesquena commented Sep 7, 2011

And debug information:

  • Check your main GUC settings to make sure that they are set to sensible values (see Tuning Your PostgreSQL Server for additional hints):
    • shared_buffers should be 10% to 25% of available RAM
    • effective_cache_size should be 75% of available RAM
  • Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a difference?
  • For Insert/Update/Delete queries, you should also try configuring your WAL:
    • Move pg_xlog to a separate disk resource, if possible
    • Increase checkpoint_segments to 16 or more (assuming you have disk space)
  • Increase wal_buffers to 16MB
@nesquena

This comment has been minimized.

Copy link
Owner Author

nesquena commented Oct 2, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.