Skip to content

Instantly share code, notes, and snippets.

@snaga

snaga/pg_stat_statements_using_pandas.ipynb

Created December 1, 2019 02:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save snaga/27f177ebf53087ae75e9a43b2ad328ec to your computer and use it in GitHub Desktop.
Save snaga/27f177ebf53087ae75e9a43b2ad328ec to your computer and use it in GitHub Desktop.
Download ZIP
pg_stat_statements_using_pandas.ipynb
Display the source blob
Display the rendered blob
Raw
pg_stat_statements_using_pandas.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# PostgreSQL Query Performance Analysis using Pandas"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Overview\n",
"\n",
"### Motivation\n",
"\n",
"* 複数時点のpg_stat_statementsのデータから、パフォーマンスが悪化しているクエリを抽出したい。\n",
"* それ以外にもいろいろな分析ができるようにしておきたい。\n",
"\n",
"### How it works\n",
"\n",
"* pg_stat_statementsのデータを時間をおいて複数回取得する。\n",
"* その時のタイムスタンプも取得する。\n",
"* 取得したデータをPandasのDataframeに読み込んで分析をする。\n",
"\n",
"### How to collect data\n",
"\n",
"* 以下のSQLコマンドでpsqlを使って情報を取得する。\n",
" * `select now();`\n",
" * `select * from pg_stat_statements;`\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Input data\n",
"\n",
"取得したタイムスタンプの文字列とpg_stat_statementsの出力を、pythonの変数として取り込む。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Data point 1"
]
},
{
"cell_type": "code",
"execution_count": 347,
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
"ts1 = '2019-11-30 18:41:46.290788+09'\n",
"\n",
"stmt1 = '''\n",
" userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time\n",
"--------+-------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------------+----------------------+----------------------+----------------------+----------------------+--------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------\n",
" 10 | 12401 | 2111245140 | begin | 2 | 0.00213333060267016 | 0.000853332241068065 | 0.0012799983616021 | 0.00106666530133508 | 0.000213333060267016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 73491916 | /*pga4dash*/ +| 1322 | 17165.5204281338 | 0.386559505203833 | 132.164950828863 | 12.9845086445793 | 7.6538449514591 | 1322 | 3966 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS \"Transactions\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) FROM pg_stat_database) AS \"Commits\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_rollback) FROM pg_stat_database) AS \"Rollbacks\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 3571892116 | BEGIN; | 8000 | 6.89236451110624 | 0.000426666120534032 | 0.0277332978347121 | 0.000861545563888342 | 0.000442352042060711 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 821356254 | create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=100) | 1 | 1.82399766528299 | 1.82399766528299 | 1.82399766528299 | 1.82399766528299 | 0 | 0 | 144 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3521336495 | insert into pgbench_branches(bid,bbalance) values(?,?) | 1 | 0.78506566178262 | 0.78506566178262 | 0.78506566178262 | 0.78506566178262 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 1246692062 | +| 2 | 0.091733215914817 | 0.0375466186069948 | 0.0541865973078221 | 0.0458666079574085 | 0.00831998935041363 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | oid as id, rolname as name, rolsuper as is_superuser, +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolcreaterole as can_create_role, rolcreatedb as can_create_db +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_roles +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolname = current_user | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 732908238 | SELECT oid, format_type(oid, ?) AS typname FROM pg_type WHERE oid IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY oid; | 1 | 0.945065456982882 | 0.945065456982882 | 0.945065456982882 | 0.945065456982882 | 0 | 4 | 4 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 3796226592 | SELECT n.nspname as \"Schema\", +| 1 | 1.32693163486084 | 1.32693163486084 | 1.32693163486084 | 1.32693163486084 | 0 | 5 | 563 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | c.relname as \"Name\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | CASE c.relkind WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END as \"Type\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\" +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM pg_catalog.pg_class c +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE c.relkind IN (?,?,?,?,?,?) +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname <> ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname <> ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname !~ ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND pg_catalog.pg_table_is_visible(c.oid) +| | | | | | | | | | | | | | | | | | |\n",
" | | | ORDER BY 1,2; | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2684659148 | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; | 8000 | 105478.16728128 | 0.0183466431829634 | 352.118162622085 | 13.1847709101599 | 25.6086730478685 | 8000 | 61263 | 4 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2846687831 | /*pga4dash*/ +| 223 | 1321.35126867038 | 0.402346151663593 | 46.6384469694545 | 5.92534201197478 | 8.41008722767042 | 223 | 892 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(blks_read) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Reads\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(blks_hit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Hits\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2525952913 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP); | 8000 | 125.034506622498 | 0.00810665629014662 | 4.81578050246762 | 0.0156293133278123 | 0.0619280039369677 | 8000 | 8109 | 59 | 57 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 4050214184 | copy pgbench_accounts from stdin | 1 | 277.562098053848 | 277.562098053848 | 277.562098053848 | 277.562098053848 | 0 | 100000 | 6 | 1640 | 1640 | 1640 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1392856018 | create extension pg_stat_statements; | 1 | 82.4199478358001 | 82.4199478358001 | 82.4199478358001 | 82.4199478358001 | 0 | 0 | 672 | 96 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1891407511 | SET DateStyle=ISO;SET client_min_messages=notice;SET bytea_output=escape;SET client_encoding='UNICODE'; | 8 | 0.0635732519595708 | 0.0012799983616021 | 0.0217599721472357 | 0.00794665649494635 | 0.00712972205168377 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2044555847 | select now() +| 1 | 0.0537599311872881 | 0.0537599311872881 | 0.0537599311872881 | 0.0537599311872881 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | ; | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2778072147 | +| 2 | 0.100693204446032 | 0.0473599393792776 | 0.053333265066754 | 0.0503466022230158 | 0.00298666284373822 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | db.oid as did, db.datname, db.datallowconn, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_encoding_to_char(db.encoding) AS serverencoding, +| | | | | | | | | | | | | | | | | | |\n",
" | | | has_database_privilege(db.oid, ?) as cancreate, datlastsysoid +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_database db +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE db.datname = current_database() | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2892619603 | select * from pg_stat_statements; | 3 | 4.70911397233412 | 1.09141193632605 | 2.18325053877264 | 1.56970465744471 | 0.455885911501317 | 121 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 387008091 | vacuum analyze pgbench_tellers | 1 | 14.5996613124335 | 14.5996613124335 | 14.5996613124335 | 14.5996613124335 | 0 | 0 | 74 | 4 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3091166733 | create table pgbench_branches(bid int not null,bbalance int,filler char(88)) with (fillfactor=100) | 1 | 1.36746491631157 | 1.36746491631157 | 1.36746491631157 | 1.36746491631157 | 0 | 0 | 134 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1007250745 | /*pga4dash*/ +| 224 | 1364.85414632003 | 0.507306017314965 | 131.931991127051 | 6.09309886750012 | 11.3062637034927 | 224 | 1344 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_inserted) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Inserts\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_updated) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Updates\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_deleted) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Deletes\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2320730284 | vacuum pgbench_branches | 1 | 25.481780716654 | 25.481780716654 | 25.481780716654 | 25.481780716654 | 0 | 0 | 35 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1776614652 | alter table pgbench_tellers add primary key (tid) | 1 | 12.2060643762376 | 12.2060643762376 | 12.2060643762376 | 12.2060643762376 | 0 | 0 | 87 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 181165670 | drop table if exists pgbench_accounts | 1 | 0.166826453128807 | 0.166826453128807 | 0.166826453128807 | 0.166826453128807 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1896558615 | /*pga4dash*/ +| 1 | 0.515839339725645 | 0.515839339725645 | 0.515839339725645 | 0.515839339725645 | 0 | 1 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | pid, +| | | | | | | | | | | | | | | | | | |\n",
" | | | datname, +| | | | | | | | | | | | | | | | | | |\n",
" | | | usename, +| | | | | | | | | | | | | | | | | | |\n",
" | | | application_name, +| | | | | | | | | | | | | | | | | | |\n",
" | | | client_addr, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(backend_start, ?) AS backend_start, +| | | | | | | | | | | | | | | | | | |\n",
" | | | state, +| | | | | | | | | | | | | | | | | | |\n",
" | | | wait_event_type || ? || wait_event AS wait_event, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_blocking_pids(pid) AS blocking_pids, +| | | | | | | | | | | | | | | | | | |\n",
" | | | query, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(state_change, ?) AS state_change, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(query_start, ?) AS query_start +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_stat_activity +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | datname = (SELECT datname FROM pg_database WHERE oid = ?)ORDER BY pid | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 363344192 | SELECT abalance FROM pgbench_accounts WHERE aid = ?; | 8000 | 113.188975118113 | 0.00895998853121468 | 0.128426502280744 | 0.014148621889764 | 0.00401959895031047 | 8000 | 25837 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2820971117 | vacuum analyze pgbench_history | 1 | 1.65802454439525 | 1.65802454439525 | 1.65802454439525 | 1.65802454439525 | 0 | 0 | 95 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1835535055 | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; | 8000 | 167192.892339764 | 0.013226649736555 | 220.379451247636 | 20.8991115424706 | 15.8493286008614 | 8000 | 121244 | 8 | 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2768800293 | insert into pgbench_tellers(tid,bid,tbalance) values (?,?,?) | 10 | 1.03466534229503 | 0.00469332732587436 | 0.95018545042929 | 0.103466534229503 | 0.282254924769895 | 10 | 9 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1044785669 | drop table if exists pgbench_tellers | 1 | 0.114773186423655 | 0.114773186423655 | 0.114773186423655 | 0.114773186423655 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 359049866 | SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc, +| 1 | 3.04682276673353 | 3.04682276673353 | 3.04682276673353 | 3.04682276673353 | 0 | 0 | 21 | 48 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, ?) +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN tg.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ty.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ns.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN pr.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN la.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN rw.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN co.oid IS NOT NULL THEN ?::text || contype +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ad.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN fs.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN fdw.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | ELSE ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | END AS type, +| | | | | | | | | | | | | | | | | | |\n",
" | | | COALESCE(coc.relname, clrw.relname) AS ownertable, +| | | | | | | | | | | | | | | | | | |\n",
" | | | CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || ? || att.attname +| | | | | | | | | | | | | | | | | | |\n",
" | | | ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname) +| | | | | | | | | | | | | | | | | | |\n",
" | | | END AS refname, +| | | | | | | | | | | | | | | | | | |\n",
" | | | COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM pg_depend dep +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class cl ON dep.objid=cl.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_proc pr ON dep.objid=pr.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_trigger tg ON dep.objid=tg.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_type ty ON dep.objid=ty.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_constraint co ON dep.objid=co.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class coc ON co.conrelid=coc.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_language la ON dep.objid=la.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace ns ON dep.objid=ns.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_foreign_server fs ON fs.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE dep.refobjid=?::oid AND +| | | | | | | | | | | | | | | | | | |\n",
" | | | classid IN ( SELECT oid FROM pg_class WHERE relname IN +| | | | | | | | | | | | | | | | | | |\n",
" | | | (?, ?, ?, ?, ?, ?, ?, +| | | | | | | | | | | | | | | | | | |\n",
" | | | ?, ?, ?, ?, ?, ?)) +| | | | | | | | | | | | | | | | | | |\n",
" | | | ORDER BY classid, cl.relkind | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 2778072147 | +| 2 | 0.100266538325498 | 0.0447999426560734 | 0.0554665956694242 | 0.0501332691627488 | 0.0053333265066754 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | db.oid as did, db.datname, db.datallowconn, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_encoding_to_char(db.encoding) AS serverencoding, +| | | | | | | | | | | | | | | | | | |\n",
" | | | has_database_privilege(db.oid, ?) as cancreate, datlastsysoid +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_database db +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE db.datname = current_database() | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 1891407511 | SET DateStyle=ISO;SET client_min_messages=notice;SET bytea_output=escape;SET client_encoding='UNICODE'; | 8 | 0.079786564539864 | 0.00213333060267016 | 0.0298666284373823 | 0.00997332056748301 | 0.00929637210495181 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1868163425 | commit | 2 | 0.00170666448213613 | 0.000853332241068065 | 0.000853332241068065 | 0.000853332241068065 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 803007722 | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?; | 8000 | 404.965028311435 | 0.0196266415445655 | 25.5171940046583 | 0.0506206285389287 | 0.289359003750385 | 8000 | 40911 | 307 | 690 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1639697065 | drop table if exists pgbench_branches | 1 | 0.133119829606618 | 0.133119829606618 | 0.133119829606618 | 0.133119829606618 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 355420951 | vacuum analyze pgbench_accounts | 1 | 72.376227358429 | 72.376227358429 | 72.376227358429 | 72.376227358429 | 0 | 0 | 5001 | 4 | 1644 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3486217909 | select count(*) from pgbench_branches | 1 | 0.065706582562241 | 0.065706582562241 | 0.065706582562241 | 0.065706582562241 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2312510646 | create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=100) | 1 | 1.68277117938622 | 1.68277117938622 | 1.68277117938622 | 1.68277117938622 | 0 | 0 | 142 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1711809994 | vacuum pgbench_tellers | 1 | 3.416315627116 | 3.416315627116 | 3.416315627116 | 3.416315627116 | 0 | 0 | 57 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 274596750 | select * from pg_stat_statements; | 3 | 5.76468595453531 | 0.346879555994168 | 3.60234205566884 | 1.9215619848451 | 1.33115277207919 | 25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 176252718 | END; | 8000 | 5.25993993394314 | 0 | 0.00383999508480629 | 0.000657492491742943 | 0.000258060353674366 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1246692062 | +| 2 | 0.0550399295488902 | 0.0255999672320419 | 0.0294399623168482 | 0.0275199647744451 | 0.00191999754240315 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | oid as id, rolname as name, rolsuper as is_superuser, +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolcreaterole as can_create_role, rolcreatedb as can_create_db +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_roles +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolname = current_user | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 732908238 | SELECT oid, format_type(oid, ?) AS typname FROM pg_type WHERE oid IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY oid; | 1 | 0.433066112342043 | 0.433066112342043 | 0.433066112342043 | 0.433066112342043 | 0 | 4 | 8 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 509468609 | /*pga4dash*/ +| 225 | 1351.16157717985 | 0.438612771908985 | 62.0688272185678 | 6.00516256524377 | 9.12105969144042 | 225 | 1350 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Transactions\",+| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Commits\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_rollback) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Rollbacks\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2042960798 | create table pgbench_history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22)) | 1 | 5.55348622487097 | 5.55348622487097 | 5.55348622487097 | 5.55348622487097 | 0 | 0 | 263 | 1 | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2835152121 | truncate pgbench_history | 1 | 1.75829108272075 | 1.75829108272075 | 1.75829108272075 | 1.75829108272075 | 0 | 0 | 45 | 0 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 347448438 | vacuum analyze pgbench_branches | 1 | 44.9932224086753 | 44.9932224086753 | 44.9932224086753 | 44.9932224086753 | 0 | 0 | 155 | 17 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 415251803 | truncate pgbench_accounts | 1 | 1.75274442315381 | 1.75274442315381 | 1.75274442315381 | 1.75274442315381 | 0 | 0 | 28 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2417543957 | drop table if exists pgbench_history | 1 | 0.89301219027773 | 0.89301219027773 | 0.89301219027773 | 0.89301219027773 | 0 | 0 | 9 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3640595101 | /*pga4dash*/ +| 223 | 1814.37975759391 | 0.419839462605488 | 132.172204152912 | 8.13623209683368 | 11.7405399853151 | 223 | 892 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_fetched) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Fetched\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_returned) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Returned\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 3921192557 | alter table pgbench_accounts add primary key (aid) | 1 | 56.9667537492219 | 56.9667537492219 | 56.9667537492219 | 56.9667537492219 | 0 | 0 | 1726 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3464398693 | alter table pgbench_branches add primary key (bid) | 1 | 27.6705779149936 | 27.6705779149936 | 27.6705779149936 | 27.6705779149936 | 0 | 0 | 115 | 17 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 927919161 | /*pga4dash*/ +| 228 | 54.0415308268406 | 0.139093155294095 | 0.753492368863101 | 0.237024258012459 | 0.0803793833218495 | 228 | 3034 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Total\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Active\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Idle\" | | | | | | | | | | | | | | | | | | |\n",
"'''"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Data point 2"
]
},
{
"cell_type": "code",
"execution_count": 348,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"ts2 = '2019-11-30 18:49:11.138668+09'\n",
"\n",
"stmt2 = '''\n",
" userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time\n",
"--------+-------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------------+----------------------+----------------------+----------------------+----------------------+--------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------\n",
" 10 | 12401 | 2111245140 | begin | 2 | 0.00213333060267016 | 0.000853332241068065 | 0.0012799983616021 | 0.00106666530133508 | 0.000213333060267016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 73491916 | /*pga4dash*/ +| 1322 | 17165.5204281338 | 0.386559505203833 | 132.164950828863 | 12.9845086445793 | 7.6538449514591 | 1322 | 3966 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS \"Transactions\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) FROM pg_stat_database) AS \"Commits\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_rollback) FROM pg_stat_database) AS \"Rollbacks\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 3571892116 | BEGIN; | 32000 | 27.9705241977259 | 0.000426666120534032 | 0.217599721472357 | 0.000874078881179033 | 0.00134599116890715 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 821356254 | create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=100) | 1 | 1.82399766528299 | 1.82399766528299 | 1.82399766528299 | 1.82399766528299 | 0 | 0 | 144 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3521336495 | insert into pgbench_branches(bid,bbalance) values(?,?) | 1 | 0.78506566178262 | 0.78506566178262 | 0.78506566178262 | 0.78506566178262 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 1246692062 | +| 2 | 0.091733215914817 | 0.0375466186069948 | 0.0541865973078221 | 0.0458666079574085 | 0.00831998935041363 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | oid as id, rolname as name, rolsuper as is_superuser, +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolcreaterole as can_create_role, rolcreatedb as can_create_db +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_roles +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolname = current_user | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 732908238 | SELECT oid, format_type(oid, ?) AS typname FROM pg_type WHERE oid IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY oid; | 1 | 0.945065456982882 | 0.945065456982882 | 0.945065456982882 | 0.945065456982882 | 0 | 4 | 4 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 3796226592 | SELECT n.nspname as \"Schema\", +| 1 | 1.32693163486084 | 1.32693163486084 | 1.32693163486084 | 1.32693163486084 | 0 | 5 | 563 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | c.relname as \"Name\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | CASE c.relkind WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END as \"Type\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\" +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM pg_catalog.pg_class c +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE c.relkind IN (?,?,?,?,?,?) +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname <> ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname <> ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname !~ ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND pg_catalog.pg_table_is_visible(c.oid) +| | | | | | | | | | | | | | | | | | |\n",
" | | | ORDER BY 1,2; | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2684659148 | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; | 32000 | 425820.079590296 | 0.0183466431829634 | 393.00472362062 | 13.3068774871968 | 25.668919171568 | 32000 | 250053 | 4 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2846687831 | /*pga4dash*/ +| 367 | 2539.81456237069 | 0.402346151663593 | 131.490391692299 | 6.92047564678663 | 10.5989470201755 | 367 | 1468 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(blks_read) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Reads\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(blks_hit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Hits\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2525952913 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP); | 32000 | 579.404058362853 | 0.00810665629014662 | 30.803587238075 | 0.0181063768238375 | 0.214572017281579 | 32000 | 32470 | 228 | 224 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 4050214184 | copy pgbench_accounts from stdin | 1 | 277.562098053848 | 277.562098053848 | 277.562098053848 | 277.562098053848 | 0 | 100000 | 6 | 1640 | 1640 | 1640 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1392856018 | create extension pg_stat_statements; | 1 | 82.4199478358001 | 82.4199478358001 | 82.4199478358001 | 82.4199478358001 | 0 | 0 | 672 | 96 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1891407511 | SET DateStyle=ISO;SET client_min_messages=notice;SET bytea_output=escape;SET client_encoding='UNICODE'; | 8 | 0.0635732519595708 | 0.0012799983616021 | 0.0217599721472357 | 0.00794665649494635 | 0.00712972205168377 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2044555847 | select now() +| 3 | 0.20735973457954 | 0.0537599311872881 | 0.0853332241068065 | 0.0691199115265133 | 0.0129038582958913 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | ; | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2778072147 | +| 2 | 0.100693204446032 | 0.0473599393792776 | 0.053333265066754 | 0.0503466022230158 | 0.00298666284373822 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | db.oid as did, db.datname, db.datallowconn, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_encoding_to_char(db.encoding) AS serverencoding, +| | | | | | | | | | | | | | | | | | |\n",
" | | | has_database_privilege(db.oid, ?) as cancreate, datlastsysoid +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_database db +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE db.datname = current_database() | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2892619603 | select * from pg_stat_statements; | 4 | 6.51689832503681 | 1.09141193632605 | 2.18325053877264 | 1.6292245812592 | 0.408046366509605 | 174 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 387008091 | vacuum analyze pgbench_tellers | 1 | 14.5996613124335 | 14.5996613124335 | 14.5996613124335 | 14.5996613124335 | 0 | 0 | 74 | 4 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3091166733 | create table pgbench_branches(bid int not null,bbalance int,filler char(88)) with (fillfactor=100) | 1 | 1.36746491631157 | 1.36746491631157 | 1.36746491631157 | 1.36746491631157 | 0 | 0 | 134 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1007250745 | /*pga4dash*/ +| 366 | 3148.49047659886 | 0.505599352832828 | 131.931991127051 | 8.60243299617174 | 14.0133516242248 | 366 | 2196 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_inserted) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Inserts\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_updated) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Updates\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_deleted) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Deletes\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2320730284 | vacuum pgbench_branches | 2 | 57.7573660705714 | 25.481780716654 | 32.2755853539174 | 28.8786830352857 | 3.3969023186317 | 0 | 87 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1776614652 | alter table pgbench_tellers add primary key (tid) | 1 | 12.2060643762376 | 12.2060643762376 | 12.2060643762376 | 12.2060643762376 | 0 | 0 | 87 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 181165670 | drop table if exists pgbench_accounts | 1 | 0.166826453128807 | 0.166826453128807 | 0.166826453128807 | 0.166826453128807 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1896558615 | /*pga4dash*/ +| 1 | 0.515839339725645 | 0.515839339725645 | 0.515839339725645 | 0.515839339725645 | 0 | 1 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | pid, +| | | | | | | | | | | | | | | | | | |\n",
" | | | datname, +| | | | | | | | | | | | | | | | | | |\n",
" | | | usename, +| | | | | | | | | | | | | | | | | | |\n",
" | | | application_name, +| | | | | | | | | | | | | | | | | | |\n",
" | | | client_addr, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(backend_start, ?) AS backend_start, +| | | | | | | | | | | | | | | | | | |\n",
" | | | state, +| | | | | | | | | | | | | | | | | | |\n",
" | | | wait_event_type || ? || wait_event AS wait_event, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_blocking_pids(pid) AS blocking_pids, +| | | | | | | | | | | | | | | | | | |\n",
" | | | query, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(state_change, ?) AS state_change, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(query_start, ?) AS query_start +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_stat_activity +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | datname = (SELECT datname FROM pg_database WHERE oid = ?)ORDER BY pid | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 363344192 | SELECT abalance FROM pgbench_accounts WHERE aid = ?; | 32000 | 450.467690068046 | 0.00853332241068065 | 0.253439675597215 | 0.0140771153146257 | 0.00547012526192599 | 32000 | 98266 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2820971117 | vacuum analyze pgbench_history | 1 | 1.65802454439525 | 1.65802454439525 | 1.65802454439525 | 1.65802454439525 | 0 | 0 | 95 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1835535055 | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; | 32000 | 655066.433194961 | 0.0102399868928168 | 220.379451247636 | 20.4708260373428 | 15.5295432048718 | 32000 | 421790 | 8 | 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2768800293 | insert into pgbench_tellers(tid,bid,tbalance) values (?,?,?) | 10 | 1.03466534229503 | 0.00469332732587436 | 0.95018545042929 | 0.103466534229503 | 0.282254924769895 | 10 | 9 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1044785669 | drop table if exists pgbench_tellers | 1 | 0.114773186423655 | 0.114773186423655 | 0.114773186423655 | 0.114773186423655 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 359049866 | SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc, +| 1 | 3.04682276673353 | 3.04682276673353 | 3.04682276673353 | 3.04682276673353 | 0 | 0 | 21 | 48 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, ?) +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN tg.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ty.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ns.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN pr.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN la.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN rw.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN co.oid IS NOT NULL THEN ?::text || contype +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ad.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN fs.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN fdw.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | ELSE ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | END AS type, +| | | | | | | | | | | | | | | | | | |\n",
" | | | COALESCE(coc.relname, clrw.relname) AS ownertable, +| | | | | | | | | | | | | | | | | | |\n",
" | | | CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || ? || att.attname +| | | | | | | | | | | | | | | | | | |\n",
" | | | ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname) +| | | | | | | | | | | | | | | | | | |\n",
" | | | END AS refname, +| | | | | | | | | | | | | | | | | | |\n",
" | | | COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM pg_depend dep +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class cl ON dep.objid=cl.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_proc pr ON dep.objid=pr.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_trigger tg ON dep.objid=tg.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_type ty ON dep.objid=ty.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_constraint co ON dep.objid=co.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class coc ON co.conrelid=coc.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_language la ON dep.objid=la.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace ns ON dep.objid=ns.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_foreign_server fs ON fs.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE dep.refobjid=?::oid AND +| | | | | | | | | | | | | | | | | | |\n",
" | | | classid IN ( SELECT oid FROM pg_class WHERE relname IN +| | | | | | | | | | | | | | | | | | |\n",
" | | | (?, ?, ?, ?, ?, ?, ?, +| | | | | | | | | | | | | | | | | | |\n",
" | | | ?, ?, ?, ?, ?, ?)) +| | | | | | | | | | | | | | | | | | |\n",
" | | | ORDER BY classid, cl.relkind | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 2778072147 | +| 2 | 0.100266538325498 | 0.0447999426560734 | 0.0554665956694242 | 0.0501332691627488 | 0.0053333265066754 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | db.oid as did, db.datname, db.datallowconn, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_encoding_to_char(db.encoding) AS serverencoding, +| | | | | | | | | | | | | | | | | | |\n",
" | | | has_database_privilege(db.oid, ?) as cancreate, datlastsysoid +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_database db +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE db.datname = current_database() | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 1891407511 | SET DateStyle=ISO;SET client_min_messages=notice;SET bytea_output=escape;SET client_encoding='UNICODE'; | 8 | 0.079786564539864 | 0.00213333060267016 | 0.0298666284373823 | 0.00997332056748301 | 0.00929637210495181 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1868163425 | commit | 2 | 0.00170666448213613 | 0.000853332241068065 | 0.000853332241068065 | 0.000853332241068065 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 803007722 | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?; | 32000 | 1500.97642541682 | 0.0162133125802932 | 36.87163280431 | 0.0469055132942766 | 0.254467646892253 | 32000 | 139200 | 331 | 2459 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1639697065 | drop table if exists pgbench_branches | 1 | 0.133119829606618 | 0.133119829606618 | 0.133119829606618 | 0.133119829606618 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 355420951 | vacuum analyze pgbench_accounts | 1 | 72.376227358429 | 72.376227358429 | 72.376227358429 | 72.376227358429 | 0 | 0 | 5001 | 4 | 1644 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3486217909 | select count(*) from pgbench_branches | 4 | 8.84948200599636 | 0.065706582562241 | 7.86388326756275 | 2.21237050149909 | 3.26708369432408 | 4 | 9 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2312510646 | create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=100) | 1 | 1.68277117938622 | 1.68277117938622 | 1.68277117938622 | 1.68277117938622 | 0 | 0 | 142 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1711809994 | vacuum pgbench_tellers | 2 | 7.22943074632864 | 3.416315627116 | 3.81311511921265 | 3.61471537316432 | 0.198399746048325 | 0 | 123 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 274596750 | select * from pg_stat_statements; | 3 | 5.76468595453531 | 0.346879555994168 | 3.60234205566884 | 1.9215619848451 | 1.33115277207919 | 25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 176252718 | END; | 32000 | 21.3115460545554 | 0 | 0.078506566178262 | 0.00066598581420483 | 0.00053799119879461 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1246692062 | +| 2 | 0.0550399295488902 | 0.0255999672320419 | 0.0294399623168482 | 0.0275199647744451 | 0.00191999754240315 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | oid as id, rolname as name, rolsuper as is_superuser, +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolcreaterole as can_create_role, rolcreatedb as can_create_db +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_roles +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolname = current_user | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 732908238 | SELECT oid, format_type(oid, ?) AS typname FROM pg_type WHERE oid IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY oid; | 1 | 0.433066112342043 | 0.433066112342043 | 0.433066112342043 | 0.433066112342043 | 0 | 4 | 8 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 509468609 | /*pga4dash*/ +| 369 | 2447.75238687694 | 0.438612771908985 | 62.0688272185678 | 6.63347530318955 | 9.08485489974567 | 369 | 2214 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Transactions\",+| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Commits\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_rollback) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Rollbacks\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2042960798 | create table pgbench_history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22)) | 1 | 5.55348622487097 | 5.55348622487097 | 5.55348622487097 | 5.55348622487097 | 0 | 0 | 263 | 1 | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2835152121 | truncate pgbench_history | 2 | 3.26783581717015 | 1.50954473444941 | 1.75829108272075 | 1.63391790858508 | 0.12437317413567 | 0 | 88 | 0 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 347448438 | vacuum analyze pgbench_branches | 1 | 44.9932224086753 | 44.9932224086753 | 44.9932224086753 | 44.9932224086753 | 0 | 0 | 155 | 17 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 415251803 | truncate pgbench_accounts | 1 | 1.75274442315381 | 1.75274442315381 | 1.75274442315381 | 1.75274442315381 | 0 | 0 | 28 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2417543957 | drop table if exists pgbench_history | 1 | 0.89301219027773 | 0.89301219027773 | 0.89301219027773 | 0.89301219027773 | 0 | 0 | 9 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3640595101 | /*pga4dash*/ +| 370 | 3236.93452339048 | 0.371199524864608 | 132.172204152912 | 8.74847168483913 | 12.5523713576049 | 370 | 1480 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_fetched) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Fetched\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_returned) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Returned\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 3921192557 | alter table pgbench_accounts add primary key (aid) | 1 | 56.9667537492219 | 56.9667537492219 | 56.9667537492219 | 56.9667537492219 | 0 | 0 | 1726 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3464398693 | alter table pgbench_branches add primary key (bid) | 1 | 27.6705779149936 | 27.6705779149936 | 27.6705779149936 | 27.6705779149936 | 0 | 0 | 115 | 17 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 927919161 | /*pga4dash*/ +| 374 | 89.6250586132583 | 0.139093155294095 | 0.753492368863101 | 0.239639194153097 | 0.0834394321122186 | 374 | 5583 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Total\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Active\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Idle\" | | | | | | | | | | | | | | | | | | |\n",
"'''"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Data point 3"
]
},
{
"cell_type": "code",
"execution_count": 349,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"ts3 = '2019-11-30 19:11:52.26348+09'\n",
"\n",
"stmt3 = '''\n",
" userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time\n",
"--------+-------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------------+----------------------+----------------------+----------------------+----------------------+--------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------\n",
" 10 | 12401 | 2111245140 | begin | 2 | 0.00213333060267016 | 0.000853332241068065 | 0.0012799983616021 | 0.00106666530133508 | 0.000213333060267016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 73491916 | /*pga4dash*/ +| 1322 | 17165.5204281338 | 0.386559505203833 | 132.164950828863 | 12.9845086445793 | 7.6538449514591 | 1322 | 3966 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS \"Transactions\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) FROM pg_stat_database) AS \"Commits\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_rollback) FROM pg_stat_database) AS \"Rollbacks\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 3571892116 | BEGIN; | 41029 | 35.8489141133981 | 0.000426666120534032 | 0.217599721472357 | 0.000873745743581119 | 0.00130967674152819 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 821356254 | create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=100) | 1 | 1.82399766528299 | 1.82399766528299 | 1.82399766528299 | 1.82399766528299 | 0 | 0 | 144 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3521336495 | insert into pgbench_branches(bid,bbalance) values(?,?) | 1 | 0.78506566178262 | 0.78506566178262 | 0.78506566178262 | 0.78506566178262 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 1246692062 | +| 2 | 0.091733215914817 | 0.0375466186069948 | 0.0541865973078221 | 0.0458666079574085 | 0.00831998935041363 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | oid as id, rolname as name, rolsuper as is_superuser, +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolcreaterole as can_create_role, rolcreatedb as can_create_db +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_roles +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolname = current_user | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 732908238 | SELECT oid, format_type(oid, ?) AS typname FROM pg_type WHERE oid IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY oid; | 1 | 0.945065456982882 | 0.945065456982882 | 0.945065456982882 | 0.945065456982882 | 0 | 4 | 4 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 3796226592 | SELECT n.nspname as \"Schema\", +| 1 | 1.32693163486084 | 1.32693163486084 | 1.32693163486084 | 1.32693163486084 | 0 | 5 | 563 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | c.relname as \"Name\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | CASE c.relkind WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END as \"Type\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\" +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM pg_catalog.pg_class c +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE c.relkind IN (?,?,?,?,?,?) +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname <> ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname <> ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND n.nspname !~ ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | AND pg_catalog.pg_table_is_visible(c.oid) +| | | | | | | | | | | | | | | | | | |\n",
" | | | ORDER BY 1,2; | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2684659148 | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; | 41028 | 546177.145373251 | 0.0183466431829634 | 393.00472362062 | 13.3123024610815 | 25.8543222518821 | 41028 | 320885 | 4 | 22 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2846687831 | /*pga4dash*/ +| 1015 | 9026.5757526497 | 0.402346151663593 | 133.595135664893 | 8.89317808142828 | 13.4739225963287 | 1015 | 4060 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(blks_read) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Reads\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(blks_hit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Hits\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2525952913 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP); | 41015 | 786.451900008308 | 0.00810665629014662 | 37.8512582170561 | 0.0191747385105018 | 0.272985204499692 | 41015 | 41626 | 295 | 289 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 4050214184 | copy pgbench_accounts from stdin | 1 | 277.562098053848 | 277.562098053848 | 277.562098053848 | 277.562098053848 | 0 | 100000 | 6 | 1640 | 1640 | 1640 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1392856018 | create extension pg_stat_statements; | 1 | 82.4199478358001 | 82.4199478358001 | 82.4199478358001 | 82.4199478358001 | 0 | 0 | 672 | 96 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1891407511 | SET DateStyle=ISO;SET client_min_messages=notice;SET bytea_output=escape;SET client_encoding='UNICODE'; | 8 | 0.0635732519595708 | 0.0012799983616021 | 0.0217599721472357 | 0.00794665649494635 | 0.00712972205168377 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2044555847 | select now() +| 4 | 0.287146299119404 | 0.0537599311872881 | 0.0853332241068065 | 0.071786574779851 | 0.0120919579849765 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | ; | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2778072147 | +| 2 | 0.100693204446032 | 0.0473599393792776 | 0.053333265066754 | 0.0503466022230158 | 0.00298666284373822 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | db.oid as did, db.datname, db.datallowconn, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_encoding_to_char(db.encoding) AS serverencoding, +| | | | | | | | | | | | | | | | | | |\n",
" | | | has_database_privilege(db.oid, ?) as cancreate, datlastsysoid +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_database db +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE db.datname = current_database() | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2892619603 | select * from pg_stat_statements; | 5 | 8.53545574128332 | 1.09141193632605 | 2.18325053877264 | 1.70709114825666 | 0.396805089182627 | 227 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 387008091 | vacuum analyze pgbench_tellers | 1 | 14.5996613124335 | 14.5996613124335 | 14.5996613124335 | 14.5996613124335 | 0 | 0 | 74 | 4 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3091166733 | create table pgbench_branches(bid int not null,bbalance int,filler char(88)) with (fillfactor=100) | 1 | 1.36746491631157 | 1.36746491631157 | 1.36746491631157 | 1.36746491631157 | 0 | 0 | 134 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1007250745 | /*pga4dash*/ +| 1013 | 8283.37670394448 | 0.444586097596462 | 133.117269609895 | 8.177074732423 | 11.7200698562505 | 1013 | 6078 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_inserted) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Inserts\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_updated) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Updates\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_deleted) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Deletes\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2320730284 | vacuum pgbench_branches | 3 | 77.986460177331 | 20.2290941067595 | 32.2755853539174 | 25.995486725777 | 4.93135601633868 | 0 | 133 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1776614652 | alter table pgbench_tellers add primary key (tid) | 1 | 12.2060643762376 | 12.2060643762376 | 12.2060643762376 | 12.2060643762376 | 0 | 0 | 87 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 181165670 | drop table if exists pgbench_accounts | 1 | 0.166826453128807 | 0.166826453128807 | 0.166826453128807 | 0.166826453128807 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1896558615 | /*pga4dash*/ +| 1 | 0.515839339725645 | 0.515839339725645 | 0.515839339725645 | 0.515839339725645 | 0 | 1 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | pid, +| | | | | | | | | | | | | | | | | | |\n",
" | | | datname, +| | | | | | | | | | | | | | | | | | |\n",
" | | | usename, +| | | | | | | | | | | | | | | | | | |\n",
" | | | application_name, +| | | | | | | | | | | | | | | | | | |\n",
" | | | client_addr, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(backend_start, ?) AS backend_start, +| | | | | | | | | | | | | | | | | | |\n",
" | | | state, +| | | | | | | | | | | | | | | | | | |\n",
" | | | wait_event_type || ? || wait_event AS wait_event, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_blocking_pids(pid) AS blocking_pids, +| | | | | | | | | | | | | | | | | | |\n",
" | | | query, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(state_change, ?) AS state_change, +| | | | | | | | | | | | | | | | | | |\n",
" | | | to_char(query_start, ?) AS query_start +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_stat_activity +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | datname = (SELECT datname FROM pg_database WHERE oid = ?)ORDER BY pid | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 363344192 | SELECT abalance FROM pgbench_accounts WHERE aid = ?; | 41029 | 581.234029353808 | 0.00853332241068065 | 0.560639282381719 | 0.0141664195898944 | 0.00644223341718545 | 41029 | 125517 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2820971117 | vacuum analyze pgbench_history | 1 | 1.65802454439525 | 1.65802454439525 | 1.65802454439525 | 1.65802454439525 | 0 | 0 | 95 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1835535055 | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; | 41023 | 841548.889750752 | 0.0102399868928168 | 246.976110537245 | 20.5140747812387 | 15.5718406774675 | 41023 | 569827 | 8 | 23 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2768800293 | insert into pgbench_tellers(tid,bid,tbalance) values (?,?,?) | 10 | 1.03466534229503 | 0.00469332732587436 | 0.95018545042929 | 0.103466534229503 | 0.282254924769895 | 10 | 9 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1044785669 | drop table if exists pgbench_tellers | 1 | 0.114773186423655 | 0.114773186423655 | 0.114773186423655 | 0.114773186423655 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 359049866 | SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc, +| 1 | 3.04682276673353 | 3.04682276673353 | 3.04682276673353 | 3.04682276673353 | 0 | 0 | 21 | 48 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, ?) +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN tg.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ty.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ns.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN pr.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN la.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN rw.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN co.oid IS NOT NULL THEN ?::text || contype +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN ad.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN fs.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHEN fdw.oid IS NOT NULL THEN ?::text +| | | | | | | | | | | | | | | | | | |\n",
" | | | ELSE ? +| | | | | | | | | | | | | | | | | | |\n",
" | | | END AS type, +| | | | | | | | | | | | | | | | | | |\n",
" | | | COALESCE(coc.relname, clrw.relname) AS ownertable, +| | | | | | | | | | | | | | | | | | |\n",
" | | | CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || ? || att.attname +| | | | | | | | | | | | | | | | | | |\n",
" | | | ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname) +| | | | | | | | | | | | | | | | | | |\n",
" | | | END AS refname, +| | | | | | | | | | | | | | | | | | |\n",
" | | | COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM pg_depend dep +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class cl ON dep.objid=cl.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_proc pr ON dep.objid=pr.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_trigger tg ON dep.objid=tg.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_type ty ON dep.objid=ty.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_constraint co ON dep.objid=co.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class coc ON co.conrelid=coc.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_language la ON dep.objid=la.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_namespace ns ON dep.objid=ns.oid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_foreign_server fs ON fs.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.objid +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE dep.refobjid=?::oid AND +| | | | | | | | | | | | | | | | | | |\n",
" | | | classid IN ( SELECT oid FROM pg_class WHERE relname IN +| | | | | | | | | | | | | | | | | | |\n",
" | | | (?, ?, ?, ?, ?, ?, ?, +| | | | | | | | | | | | | | | | | | |\n",
" | | | ?, ?, ?, ?, ?, ?)) +| | | | | | | | | | | | | | | | | | |\n",
" | | | ORDER BY classid, cl.relkind | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 2778072147 | +| 2 | 0.100266538325498 | 0.0447999426560734 | 0.0554665956694242 | 0.0501332691627488 | 0.0053333265066754 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | db.oid as did, db.datname, db.datallowconn, +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_encoding_to_char(db.encoding) AS serverencoding, +| | | | | | | | | | | | | | | | | | |\n",
" | | | has_database_privilege(db.oid, ?) as cancreate, datlastsysoid +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_database db +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE db.datname = current_database() | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 1891407511 | SET DateStyle=ISO;SET client_min_messages=notice;SET bytea_output=escape;SET client_encoding='UNICODE'; | 8 | 0.079786564539864 | 0.00213333060267016 | 0.0298666284373823 | 0.00997332056748301 | 0.00929637210495181 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1868163425 | commit | 2 | 0.00170666448213613 | 0.000853332241068065 | 0.000853332241068065 | 0.000853332241068065 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 803007722 | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?; | 41029 | 1924.7058030432 | 0.0162133125802932 | 36.87163280431 | 0.046910863122261 | 0.224920640483069 | 41029 | 176116 | 339 | 4303 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1639697065 | drop table if exists pgbench_branches | 1 | 0.133119829606618 | 0.133119829606618 | 0.133119829606618 | 0.133119829606618 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 355420951 | vacuum analyze pgbench_accounts | 1 | 72.376227358429 | 72.376227358429 | 72.376227358429 | 72.376227358429 | 0 | 0 | 5001 | 4 | 1644 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3486217909 | select count(*) from pgbench_branches | 7 | 9.71774756128312 | 0.065706582562241 | 7.86388326756275 | 1.38824965161187 | 2.64668697472477 | 7 | 18 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2312510646 | create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=100) | 1 | 1.68277117938622 | 1.68277117938622 | 1.68277117938622 | 1.68277117938622 | 0 | 0 | 142 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1711809994 | vacuum pgbench_tellers | 3 | 9.89012067397887 | 2.66068992765023 | 3.81311511921265 | 3.29670689132629 | 0.4780171843458 | 0 | 189 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 16393 | 274596750 | select * from pg_stat_statements; | 3 | 5.76468595453531 | 0.346879555994168 | 3.60234205566884 | 1.9215619848451 | 1.33115277207919 | 25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 176252718 | END; | 41015 | 27.506738124715 | 0 | 0.078506566178262 | 0.000670650691812961 | 0.000580735119837396 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 1246692062 | +| 2 | 0.0550399295488902 | 0.0255999672320419 | 0.0294399623168482 | 0.0275199647744451 | 0.00191999754240315 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | oid as id, rolname as name, rolsuper as is_superuser, +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolcreaterole as can_create_role, rolcreatedb as can_create_db +| | | | | | | | | | | | | | | | | | |\n",
" | | | FROM +| | | | | | | | | | | | | | | | | | |\n",
" | | | pg_catalog.pg_roles +| | | | | | | | | | | | | | | | | | |\n",
" | | | WHERE +| | | | | | | | | | | | | | | | | | |\n",
" | | | rolname = current_user | | | | | | | | | | | | | | | | | | |\n",
" 10 | 16393 | 732908238 | SELECT oid, format_type(oid, ?) AS typname FROM pg_type WHERE oid IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY oid; | 1 | 0.433066112342043 | 0.433066112342043 | 0.433066112342043 | 0.433066112342043 | 0 | 4 | 8 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 509468609 | /*pga4dash*/ +| 1014 | 8440.1487699429 | 0.438612771908985 | 132.157270838693 | 8.32361811631449 | 9.93621634098518 | 1014 | 6084 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Transactions\",+| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_commit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Commits\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(xact_rollback) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Rollbacks\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 2042960798 | create table pgbench_history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22)) | 1 | 5.55348622487097 | 5.55348622487097 | 5.55348622487097 | 5.55348622487097 | 0 | 0 | 263 | 1 | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2835152121 | truncate pgbench_history | 3 | 4.68052734225834 | 1.41269152508818 | 1.75829108272075 | 1.56017578075278 | 0.145561878471636 | 0 | 131 | 0 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 347448438 | vacuum analyze pgbench_branches | 1 | 44.9932224086753 | 44.9932224086753 | 44.9932224086753 | 44.9932224086753 | 0 | 0 | 155 | 17 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 415251803 | truncate pgbench_accounts | 1 | 1.75274442315381 | 1.75274442315381 | 1.75274442315381 | 1.75274442315381 | 0 | 0 | 28 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 2417543957 | drop table if exists pgbench_history | 1 | 0.89301219027773 | 0.89301219027773 | 0.89301219027773 | 0.89301219027773 | 0 | 0 | 9 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3640595101 | /*pga4dash*/ +| 1018 | 9290.1009620041 | 0.371199524864608 | 137.525157301132 | 9.12583591552464 | 12.5366067484023 | 1018 | 4072 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_fetched) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Fetched\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT sum(tup_returned) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Returned\" | | | | | | | | | | | | | | | | | | |\n",
" 10 | 12401 | 3921192557 | alter table pgbench_accounts add primary key (aid) | 1 | 56.9667537492219 | 56.9667537492219 | 56.9667537492219 | 56.9667537492219 | 0 | 0 | 1726 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 3464398693 | alter table pgbench_branches add primary key (bid) | 1 | 27.6705779149936 | 27.6705779149936 | 27.6705779149936 | 27.6705779149936 | 0 | 0 | 115 | 17 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" 10 | 12401 | 927919161 | /*pga4dash*/ +| 1027 | 232.184876136692 | 0.139093155294095 | 0.979198746625604 | 0.22608069730934 | 0.078754246552969 | 1027 | 14360 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0\n",
" | | | SELECT +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Total\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Active\", +| | | | | | | | | | | | | | | | | | |\n",
" | | | (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS \"Idle\" | | | | | | | | | | | | | | | | | | |\n",
"'''"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Data preparation\n",
"\n",
"取得したデータをPandasのDataframeに変換する。\n"
]
},
{
"cell_type": "code",
"execution_count": 350,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import datetime\n",
"\n",
"import pandas as pd\n",
"\n",
"# Convert a timestamp string into a datetime value.\n",
"def get_timestamp(ts_s):\n",
" dt = ts_s.split('+')[0].split('.')\n",
" return datetime.datetime.strptime(dt[0], '%Y-%m-%d %H:%M:%S')\n",
"\n",
"# Convert a string into a numeric value if possible.\n",
"def to_numeric(s):\n",
" try:\n",
" return int(s)\n",
" except Exception as ex:\n",
" pass\n",
" try:\n",
" return float(s)\n",
" except Exception as ex:\n",
" pass\n",
" return s\n",
"\n",
"# Convert a pg_stat_statements output string into a table as a list of lists. (columns x rows)\n",
"def _pg_stat_statements_to_lists(stmt):\n",
" # parse a text into rows\n",
" rows = [x.split('|') for x in stmt.split('\\n')]\n",
" a = []\n",
" \n",
" # parse each row\n",
" for i,row in enumerate(rows):\n",
" if len(row) == 1:\n",
" continue\n",
"\n",
" # continuous row for the query column\n",
" if len(row[0].strip()) == 0:\n",
" # FIXME: the index of query column (3) is hard-coded here.\n",
" a[len(a)-1][3] = a[len(a)-1][3] + row[3]\n",
" continue\n",
" a.append(row)\n",
"\n",
" # clean up each cell\n",
" b = []\n",
" for row in a:\n",
" b.append([to_numeric(x.strip()) for x in row])\n",
" return b\n",
"\n",
"# Convert a pg_stat_statements output string into a dataframe with the timestamp column.\n",
"def pgss_to_df(dt, stmt):\n",
" data = _pg_stat_statements_to_lists(stmt)\n",
" df = pd.DataFrame(data[1:], columns=data[0])\n",
" df.insert(0,\"datetime\", get_timestamp(dt))\n",
" return df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"3回分のデータをタイムスタンプと一緒にDataframeに変換し、ひとつのDataframeとして連結する。"
]
},
{
"cell_type": "code",
"execution_count": 351,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>datetime</th>\n",
" <th>userid</th>\n",
" <th>dbid</th>\n",
" <th>queryid</th>\n",
" <th>query</th>\n",
" <th>calls</th>\n",
" <th>total_time</th>\n",
" <th>min_time</th>\n",
" <th>max_time</th>\n",
" <th>mean_time</th>\n",
" <th>...</th>\n",
" <th>shared_blks_dirtied</th>\n",
" <th>shared_blks_written</th>\n",
" <th>local_blks_hit</th>\n",
" <th>local_blks_read</th>\n",
" <th>local_blks_dirtied</th>\n",
" <th>local_blks_written</th>\n",
" <th>temp_blks_read</th>\n",
" <th>temp_blks_written</th>\n",
" <th>blk_read_time</th>\n",
" <th>blk_write_time</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2019-11-30 18:41:46</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>73491916</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" <td>1322</td>\n",
" <td>17165.520428</td>\n",
" <td>0.38656</td>\n",
" <td>132.164951</td>\n",
" <td>12.984509</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>54</th>\n",
" <td>2019-11-30 18:49:11</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>73491916</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" <td>1322</td>\n",
" <td>17165.520428</td>\n",
" <td>0.38656</td>\n",
" <td>132.164951</td>\n",
" <td>12.984509</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>107</th>\n",
" <td>2019-11-30 19:11:52</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>73491916</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" <td>1322</td>\n",
" <td>17165.520428</td>\n",
" <td>0.38656</td>\n",
" <td>132.164951</td>\n",
" <td>12.984509</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>40</th>\n",
" <td>2019-11-30 18:41:46</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>176252718</td>\n",
" <td>END;</td>\n",
" <td>8000</td>\n",
" <td>5.259940</td>\n",
" <td>0.00000</td>\n",
" <td>0.003840</td>\n",
" <td>0.000657</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>93</th>\n",
" <td>2019-11-30 18:49:11</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>176252718</td>\n",
" <td>END;</td>\n",
" <td>32000</td>\n",
" <td>21.311546</td>\n",
" <td>0.00000</td>\n",
" <td>0.078507</td>\n",
" <td>0.000666</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 24 columns</p>\n",
"</div>"
],
"text/plain": [
" datetime userid dbid queryid \\\n",
"1 2019-11-30 18:41:46 10 12401 73491916 \n",
"54 2019-11-30 18:49:11 10 12401 73491916 \n",
"107 2019-11-30 19:11:52 10 12401 73491916 \n",
"40 2019-11-30 18:41:46 10 12401 176252718 \n",
"93 2019-11-30 18:49:11 10 12401 176252718 \n",
"\n",
" query calls total_time \\\n",
"1 /*pga4dash*/ ... 1322 17165.520428 \n",
"54 /*pga4dash*/ ... 1322 17165.520428 \n",
"107 /*pga4dash*/ ... 1322 17165.520428 \n",
"40 END; 8000 5.259940 \n",
"93 END; 32000 21.311546 \n",
"\n",
" min_time max_time mean_time ... shared_blks_dirtied \\\n",
"1 0.38656 132.164951 12.984509 ... 0 \n",
"54 0.38656 132.164951 12.984509 ... 0 \n",
"107 0.38656 132.164951 12.984509 ... 0 \n",
"40 0.00000 0.003840 0.000657 ... 0 \n",
"93 0.00000 0.078507 0.000666 ... 0 \n",
"\n",
" shared_blks_written local_blks_hit local_blks_read local_blks_dirtied \\\n",
"1 0 0 0 0 \n",
"54 0 0 0 0 \n",
"107 0 0 0 0 \n",
"40 0 0 0 0 \n",
"93 0 0 0 0 \n",
"\n",
" local_blks_written temp_blks_read temp_blks_written blk_read_time \\\n",
"1 0 0 0 0 \n",
"54 0 0 0 0 \n",
"107 0 0 0 0 \n",
"40 0 0 0 0 \n",
"93 0 0 0 0 \n",
"\n",
" blk_write_time \n",
"1 0 \n",
"54 0 \n",
"107 0 \n",
"40 0 \n",
"93 0 \n",
"\n",
"[5 rows x 24 columns]"
]
},
"execution_count": 351,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pgss1 = pgss_to_df(ts1, stmt1)\n",
"pgss2 = pgss_to_df(ts2, stmt2)\n",
"pgss3 = pgss_to_df(ts3, stmt3)\n",
"\n",
"pgss = pd.concat([pgss1,pgss2,pgss3], ignore_index=True).sort_values(by=['queryid', 'datetime'])\n",
"\n",
"pgss.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Pgbench queries"
]
},
{
"cell_type": "code",
"execution_count": 352,
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"def find_by_query(df, s):\n",
" return df.query('query.str.contains(\"' + s + '\")', engine='python').sort_values(by=['queryid', 'datetime'])\n",
"\n",
"def find_by_queryid(df, qid):\n",
" return df.query('queryid == ' + str(qid), engine='python').sort_values(by=['queryid', 'datetime'])"
]
},
{
"cell_type": "code",
"execution_count": 353,
"metadata": {
"scrolled": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>datetime</th>\n",
" <th>userid</th>\n",
" <th>dbid</th>\n",
" <th>queryid</th>\n",
" <th>query</th>\n",
" <th>calls</th>\n",
" <th>total_time</th>\n",
" <th>min_time</th>\n",
" <th>max_time</th>\n",
" <th>mean_time</th>\n",
" <th>...</th>\n",
" <th>shared_blks_dirtied</th>\n",
" <th>shared_blks_written</th>\n",
" <th>local_blks_hit</th>\n",
" <th>local_blks_read</th>\n",
" <th>local_blks_dirtied</th>\n",
" <th>local_blks_written</th>\n",
" <th>temp_blks_read</th>\n",
" <th>temp_blks_written</th>\n",
" <th>blk_read_time</th>\n",
" <th>blk_write_time</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>2019-11-30 18:41:46</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>1835535055</td>\n",
" <td>UPDATE pgbench_branches SET bbalance = bbalanc...</td>\n",
" <td>8000</td>\n",
" <td>167192.892340</td>\n",
" <td>0.013227</td>\n",
" <td>220.379451</td>\n",
" <td>20.899112</td>\n",
" <td>...</td>\n",
" <td>14</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>79</th>\n",
" <td>2019-11-30 18:49:11</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>1835535055</td>\n",
" <td>UPDATE pgbench_branches SET bbalance = bbalanc...</td>\n",
" <td>32000</td>\n",
" <td>655066.433195</td>\n",
" <td>0.010240</td>\n",
" <td>220.379451</td>\n",
" <td>20.470826</td>\n",
" <td>...</td>\n",
" <td>14</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>132</th>\n",
" <td>2019-11-30 19:11:52</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>1835535055</td>\n",
" <td>UPDATE pgbench_branches SET bbalance = bbalanc...</td>\n",
" <td>41023</td>\n",
" <td>841548.889751</td>\n",
" <td>0.010240</td>\n",
" <td>246.976111</td>\n",
" <td>20.514075</td>\n",
" <td>...</td>\n",
" <td>23</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows × 24 columns</p>\n",
"</div>"
],
"text/plain": [
" datetime userid dbid queryid \\\n",
"26 2019-11-30 18:41:46 10 12401 1835535055 \n",
"79 2019-11-30 18:49:11 10 12401 1835535055 \n",
"132 2019-11-30 19:11:52 10 12401 1835535055 \n",
"\n",
" query calls total_time \\\n",
"26 UPDATE pgbench_branches SET bbalance = bbalanc... 8000 167192.892340 \n",
"79 UPDATE pgbench_branches SET bbalance = bbalanc... 32000 655066.433195 \n",
"132 UPDATE pgbench_branches SET bbalance = bbalanc... 41023 841548.889751 \n",
"\n",
" min_time max_time mean_time ... shared_blks_dirtied \\\n",
"26 0.013227 220.379451 20.899112 ... 14 \n",
"79 0.010240 220.379451 20.470826 ... 14 \n",
"132 0.010240 246.976111 20.514075 ... 23 \n",
"\n",
" shared_blks_written local_blks_hit local_blks_read local_blks_dirtied \\\n",
"26 0 0 0 0 \n",
"79 0 0 0 0 \n",
"132 0 0 0 0 \n",
"\n",
" local_blks_written temp_blks_read temp_blks_written blk_read_time \\\n",
"26 0 0 0 0 \n",
"79 0 0 0 0 \n",
"132 0 0 0 0 \n",
"\n",
" blk_write_time \n",
"26 0 \n",
"79 0 \n",
"132 0 \n",
"\n",
"[3 rows x 24 columns]"
]
},
"execution_count": 353,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"find_by_query(pgss, 'UPDATE pgbench_branches')"
]
},
{
"cell_type": "code",
"execution_count": 354,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>datetime</th>\n",
" <th>userid</th>\n",
" <th>dbid</th>\n",
" <th>queryid</th>\n",
" <th>query</th>\n",
" <th>calls</th>\n",
" <th>total_time</th>\n",
" <th>min_time</th>\n",
" <th>max_time</th>\n",
" <th>mean_time</th>\n",
" <th>...</th>\n",
" <th>shared_blks_dirtied</th>\n",
" <th>shared_blks_written</th>\n",
" <th>local_blks_hit</th>\n",
" <th>local_blks_read</th>\n",
" <th>local_blks_dirtied</th>\n",
" <th>local_blks_written</th>\n",
" <th>temp_blks_read</th>\n",
" <th>temp_blks_written</th>\n",
" <th>blk_read_time</th>\n",
" <th>blk_write_time</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2019-11-30 18:41:46</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>2684659148</td>\n",
" <td>UPDATE pgbench_tellers SET tbalance = tbalance...</td>\n",
" <td>8000</td>\n",
" <td>105478.167281</td>\n",
" <td>0.018347</td>\n",
" <td>352.118163</td>\n",
" <td>13.184771</td>\n",
" <td>...</td>\n",
" <td>10</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>61</th>\n",
" <td>2019-11-30 18:49:11</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>2684659148</td>\n",
" <td>UPDATE pgbench_tellers SET tbalance = tbalance...</td>\n",
" <td>32000</td>\n",
" <td>425820.079590</td>\n",
" <td>0.018347</td>\n",
" <td>393.004724</td>\n",
" <td>13.306877</td>\n",
" <td>...</td>\n",
" <td>15</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>114</th>\n",
" <td>2019-11-30 19:11:52</td>\n",
" <td>10</td>\n",
" <td>12401</td>\n",
" <td>2684659148</td>\n",
" <td>UPDATE pgbench_tellers SET tbalance = tbalance...</td>\n",
" <td>41028</td>\n",
" <td>546177.145373</td>\n",
" <td>0.018347</td>\n",
" <td>393.004724</td>\n",
" <td>13.312302</td>\n",
" <td>...</td>\n",
" <td>22</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows × 24 columns</p>\n",
"</div>"
],
"text/plain": [
" datetime userid dbid queryid \\\n",
"8 2019-11-30 18:41:46 10 12401 2684659148 \n",
"61 2019-11-30 18:49:11 10 12401 2684659148 \n",
"114 2019-11-30 19:11:52 10 12401 2684659148 \n",
"\n",
" query calls total_time \\\n",
"8 UPDATE pgbench_tellers SET tbalance = tbalance... 8000 105478.167281 \n",
"61 UPDATE pgbench_tellers SET tbalance = tbalance... 32000 425820.079590 \n",
"114 UPDATE pgbench_tellers SET tbalance = tbalance... 41028 546177.145373 \n",
"\n",
" min_time max_time mean_time ... shared_blks_dirtied \\\n",
"8 0.018347 352.118163 13.184771 ... 10 \n",
"61 0.018347 393.004724 13.306877 ... 15 \n",
"114 0.018347 393.004724 13.312302 ... 22 \n",
"\n",
" shared_blks_written local_blks_hit local_blks_read local_blks_dirtied \\\n",
"8 0 0 0 0 \n",
"61 0 0 0 0 \n",
"114 0 0 0 0 \n",
"\n",
" local_blks_written temp_blks_read temp_blks_written blk_read_time \\\n",
"8 0 0 0 0 \n",
"61 0 0 0 0 \n",
"114 0 0 0 0 \n",
"\n",
" blk_write_time \n",
"8 0 \n",
"61 0 \n",
"114 0 \n",
"\n",
"[3 rows x 24 columns]"
]
},
"execution_count": 354,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"find_by_query(pgss, 'UPDATE pgbench_tellers')"
]
},
{
"cell_type": "code",
"execution_count": 355,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>queryid</th>\n",
" <th>query</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>73491916</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>40</th>\n",
" <td>176252718</td>\n",
" <td>END;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>181165670</td>\n",
" <td>drop table if exists pgbench_accounts</td>\n",
" </tr>\n",
" <tr>\n",
" <th>39</th>\n",
" <td>274596750</td>\n",
" <td>select * from pg_stat_statements;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>46</th>\n",
" <td>347448438</td>\n",
" <td>vacuum analyze pgbench_branches</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" queryid query\n",
"1 73491916 /*pga4dash*/ ...\n",
"40 176252718 END;\n",
"22 181165670 drop table if exists pgbench_accounts\n",
"39 274596750 select * from pg_stat_statements;\n",
"46 347448438 vacuum analyze pgbench_branches"
]
},
"execution_count": 355,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pgss.loc[:,['queryid','query']].drop_duplicates().head()"
]
},
{
"cell_type": "code",
"execution_count": 356,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>queryid</th>\n",
" <th>mean_time</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>107</th>\n",
" <td>73491916</td>\n",
" <td>12.984509</td>\n",
" </tr>\n",
" <tr>\n",
" <th>146</th>\n",
" <td>176252718</td>\n",
" <td>0.000671</td>\n",
" </tr>\n",
" <tr>\n",
" <th>128</th>\n",
" <td>181165670</td>\n",
" <td>0.166826</td>\n",
" </tr>\n",
" <tr>\n",
" <th>145</th>\n",
" <td>274596750</td>\n",
" <td>1.921562</td>\n",
" </tr>\n",
" <tr>\n",
" <th>152</th>\n",
" <td>347448438</td>\n",
" <td>44.993222</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" queryid mean_time\n",
"107 73491916 12.984509\n",
"146 176252718 0.000671\n",
"128 181165670 0.166826\n",
"145 274596750 1.921562\n",
"152 347448438 44.993222"
]
},
"execution_count": 356,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# average query time at the start.\n",
"df_start = pgss[pgss.datetime == pgss['datetime'].min()].loc[:,['queryid', 'mean_time']]\n",
"# average query time at the end.\n",
"df_end = pgss[pgss.datetime == pgss['datetime'].max()].loc[:,['queryid', 'mean_time']]\n",
"df_end.head()"
]
},
{
"cell_type": "code",
"execution_count": 357,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>queryid</th>\n",
" <th>mean_time_start</th>\n",
" <th>mean_time_end</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>43</th>\n",
" <td>509468609</td>\n",
" <td>6.005163</td>\n",
" <td>8.323618</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42</th>\n",
" <td>732908238</td>\n",
" <td>0.433066</td>\n",
" <td>0.945065</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>1007250745</td>\n",
" <td>6.093099</td>\n",
" <td>8.177075</td>\n",
" </tr>\n",
" <tr>\n",
" <th>41</th>\n",
" <td>1246692062</td>\n",
" <td>0.027520</td>\n",
" <td>0.045867</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>1891407511</td>\n",
" <td>0.007947</td>\n",
" <td>0.009973</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" queryid mean_time_start mean_time_end\n",
"43 509468609 6.005163 8.323618\n",
"42 732908238 0.433066 0.945065\n",
"19 1007250745 6.093099 8.177075\n",
"41 1246692062 0.027520 0.045867\n",
"13 1891407511 0.007947 0.009973"
]
},
"execution_count": 357,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# join both using the queryid.\n",
"df = df_start.join(df_end.set_index('queryid'), on='queryid', lsuffix='_start', rsuffix='_end')\n",
"\n",
"# select queries that slow down more than 10%\n",
"df = df[df.mean_time_end > df.mean_time_start * 1.1]\n",
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 358,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>mean_time_start</th>\n",
" <th>mean_time_end</th>\n",
" <th>query</th>\n",
" </tr>\n",
" <tr>\n",
" <th>queryid</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>509468609</th>\n",
" <td>6.005163</td>\n",
" <td>8.323618</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>732908238</th>\n",
" <td>0.433066</td>\n",
" <td>0.945065</td>\n",
" <td>SELECT oid, format_type(oid, ?) AS typname FRO...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1007250745</th>\n",
" <td>6.093099</td>\n",
" <td>8.177075</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1246692062</th>\n",
" <td>0.027520</td>\n",
" <td>0.045867</td>\n",
" <td>+ SELECT ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1891407511</th>\n",
" <td>0.007947</td>\n",
" <td>0.009973</td>\n",
" <td>SET DateStyle=ISO;SET client_min_messages=noti...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2044555847</th>\n",
" <td>0.053760</td>\n",
" <td>0.071787</td>\n",
" <td>select now() ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2525952913</th>\n",
" <td>0.015629</td>\n",
" <td>0.019175</td>\n",
" <td>INSERT INTO pgbench_history (tid, bid, aid, de...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2846687831</th>\n",
" <td>5.925342</td>\n",
" <td>8.893178</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3486217909</th>\n",
" <td>0.065707</td>\n",
" <td>1.388250</td>\n",
" <td>select count(*) from pgbench_branches</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3640595101</th>\n",
" <td>8.136232</td>\n",
" <td>9.125836</td>\n",
" <td>/*pga4dash*/ ...</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" mean_time_start mean_time_end \\\n",
"queryid \n",
"509468609 6.005163 8.323618 \n",
"732908238 0.433066 0.945065 \n",
"1007250745 6.093099 8.177075 \n",
"1246692062 0.027520 0.045867 \n",
"1891407511 0.007947 0.009973 \n",
"2044555847 0.053760 0.071787 \n",
"2525952913 0.015629 0.019175 \n",
"2846687831 5.925342 8.893178 \n",
"3486217909 0.065707 1.388250 \n",
"3640595101 8.136232 9.125836 \n",
"\n",
" query \n",
"queryid \n",
"509468609 /*pga4dash*/ ... \n",
"732908238 SELECT oid, format_type(oid, ?) AS typname FRO... \n",
"1007250745 /*pga4dash*/ ... \n",
"1246692062 + SELECT ... \n",
"1891407511 SET DateStyle=ISO;SET client_min_messages=noti... \n",
"2044555847 select now() ... \n",
"2525952913 INSERT INTO pgbench_history (tid, bid, aid, de... \n",
"2846687831 /*pga4dash*/ ... \n",
"3486217909 select count(*) from pgbench_branches \n",
"3640595101 /*pga4dash*/ ... "
]
},
"execution_count": 358,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# join query strings using the queryid\n",
"df.set_index('queryid').join(pgss.loc[:,['queryid','query']].set_index('queryid')).drop_duplicates()"
]
},
{
"cell_type": "code",
"execution_count": 359,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/plain": [
"'+ SELECT + oid as id, rolname as name, rolsuper as is_superuser, + rolcreaterole as can_create_role, rolcreatedb as can_create_db + FROM + pg_catalog.pg_roles + WHERE + rolname = current_user'"
]
},
"execution_count": 359,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def query_string(df, queryid):\n",
" return list(df[df.queryid==queryid]['query'])[0]\n",
"\n",
"query_string(pgss, 1246692062)"
]
},
{
"cell_type": "code",
"execution_count": 360,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"%matplotlib notebook\n",
"import matplotlib.pyplot as plt"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.3"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment