1 | 'use strict'
|
2 |
|
3 | const cli = require('heroku-cli-util')
|
4 |
|
5 | async function run(context, heroku) {
|
6 | const fetcher = require('../lib/fetcher')(heroku)
|
7 | const psql = require('../lib/psql')
|
8 |
|
9 | let db = await fetcher.database(context.app, context.args.database)
|
10 |
|
11 | let query = `
|
12 | WITH constants AS (
|
13 | SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma
|
14 | ), bloat_info AS (
|
15 | SELECT
|
16 | ma,bs,schemaname,tablename,
|
17 | (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
|
18 | (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
|
19 | FROM (
|
20 | SELECT
|
21 | schemaname, tablename, hdr, ma, bs,
|
22 | SUM((1-null_frac)*avg_width) AS datawidth,
|
23 | MAX(null_frac) AS maxfracsum,
|
24 | hdr+(
|
25 | SELECT 1+count(*)/8
|
26 | FROM pg_stats s2
|
27 | WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
|
28 | ) AS nullhdr
|
29 | FROM pg_stats s, constants
|
30 | GROUP BY 1,2,3,4,5
|
31 | ) AS foo
|
32 | ), table_bloat AS (
|
33 | SELECT
|
34 | schemaname, tablename, cc.relpages, bs,
|
35 | CEIL((cc.reltuples*((datahdr+ma-
|
36 | (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
|
37 | FROM bloat_info
|
38 | JOIN pg_class cc ON cc.relname = bloat_info.tablename
|
39 | JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
|
40 | ), index_bloat AS (
|
41 | SELECT
|
42 | schemaname, tablename, bs,
|
43 | COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
|
44 | COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
|
45 | FROM bloat_info
|
46 | JOIN pg_class cc ON cc.relname = bloat_info.tablename
|
47 | JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
|
48 | JOIN pg_index i ON indrelid = cc.oid
|
49 | JOIN pg_class c2 ON c2.oid = i.indexrelid
|
50 | )
|
51 | SELECT
|
52 | type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste
|
53 | FROM
|
54 | (SELECT
|
55 | 'table' as type,
|
56 | schemaname,
|
57 | tablename as object_name,
|
58 | ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,
|
59 | CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste
|
60 | FROM
|
61 | table_bloat
|
62 | UNION
|
63 | SELECT
|
64 | 'index' as type,
|
65 | schemaname,
|
66 | tablename || '::' || iname as object_name,
|
67 | ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,
|
68 | CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
|
69 | FROM
|
70 | index_bloat) bloat_summary
|
71 | ORDER BY raw_waste DESC, bloat DESC
|
72 | `
|
73 |
|
74 | let output = await psql.exec(db, query)
|
75 | process.stdout.write(output)
|
76 | }
|
77 |
|
78 | const cmd = {
|
79 | topic: 'pg',
|
80 | description: 'show table and index bloat in your database ordered by most wasteful',
|
81 | needsApp: true,
|
82 | needsAuth: true,
|
83 | args: [{ name: 'database', optional: true }],
|
84 | run: cli.command({ preauth: true }, run)
|
85 | }
|
86 |
|
87 | module.exports = [
|
88 | Object.assign({ command: 'bloat' }, cmd)
|
89 | ]
|