1 | 'use strict'
|
2 |
|
3 | const cli = require('heroku-cli-util')
|
4 | const psql = require('../lib/psql')
|
5 |
|
6 | async function ensurePGStatStatement(db) {
|
7 | let query = `
|
8 | SELECT exists(
|
9 | SELECT 1 FROM pg_extension e LEFT JOIN pg_namespace n ON n.oid = e.extnamespace
|
10 | WHERE e.extname='pg_stat_statements' AND n.nspname = 'public'
|
11 | ) AS available`
|
12 | let output = await psql.exec(db, query)
|
13 |
|
14 | if (!output.includes('t')) {
|
15 | throw new Error(`pg_stat_statements extension need to be installed in the public schema first.
|
16 | You can install it by running: CREATE EXTENSION pg_stat_statements;`)
|
17 | }
|
18 | }
|
19 |
|
20 | async function run(context, heroku) {
|
21 | const fetcher = require('../lib/fetcher')
|
22 |
|
23 | const { app, args, flags } = context
|
24 | const { database } = args
|
25 |
|
26 | let db = await fetcher(heroku).database(app, database)
|
27 |
|
28 | await ensurePGStatStatement(db)
|
29 |
|
30 | if (flags.reset) {
|
31 | await psql.exec(db, 'SELECT pg_stat_statements_reset()')
|
32 | return
|
33 | }
|
34 |
|
35 | let truncatedQueryString = flags.truncate
|
36 | ? 'CASE WHEN length(query) <= 40 THEN query ELSE substr(query, 0, 39) || \'…\' END'
|
37 | : 'query'
|
38 |
|
39 | let limit = 10
|
40 | if (context.flags.num) {
|
41 | if (/^(\d+)$/.exec(flags.num)) {
|
42 | limit = parseInt(flags.num)
|
43 | } else {
|
44 | throw new Error(`Cannot parse num param value "${flags.num}" to a number`)
|
45 | }
|
46 | }
|
47 |
|
48 | let query = `
|
49 | SELECT interval '1 millisecond' * total_time AS total_exec_time,
|
50 | to_char((total_time/sum(total_time) OVER()) * 100, 'FM90D0') || '%' AS prop_exec_time,
|
51 | to_char(calls, 'FM999G999G999G990') AS ncalls,
|
52 | interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time,
|
53 | ${truncatedQueryString} AS query
|
54 | FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
|
55 | ORDER BY total_time DESC
|
56 | LIMIT ${limit}
|
57 | `
|
58 |
|
59 | let output = await psql.exec(db, query)
|
60 | process.stdout.write(output)
|
61 | }
|
62 |
|
63 | module.exports = {
|
64 | topic: 'pg',
|
65 | command: 'outliers',
|
66 | description: 'show 10 queries that have longest execution time in aggregate',
|
67 | needsApp: true,
|
68 | needsAuth: true,
|
69 | args: [{ name: 'database', optional: true }],
|
70 | flags: [
|
71 | { name: 'reset', description: 'resets statistics gathered by pg_stat_statements' },
|
72 | { name: 'truncate', char: 't', description: 'truncate queries to 40 characters' },
|
73 | { name: 'num', char: 'n', description: 'the number of queries to display (default: 10)', hasValue: true }
|
74 | ],
|
75 | run: cli.command({ preauth: true }, run)
|
76 | }
|