From 14aa01d4f1669dd385da2fb3dfeb8f1f55a4b8df Mon Sep 17 00:00:00 2001 From: Ben Burwell Date: Tue, 2 Jun 2020 22:50:01 -0400 Subject: Further reorg --- go.sum | 2 + plan.go | 338 --------------------------------------------------- plan_view.go | 32 ++++- postgres/plan.go | 338 +++++++++++++++++++++++++++++++++++++++++++++++++++ postgres/postgres.go | 35 +++++- 5 files changed, 399 insertions(+), 346 deletions(-) delete mode 100644 plan.go create mode 100644 postgres/plan.go diff --git a/go.sum b/go.sum index 2beec68..2f0b933 100644 --- a/go.sum +++ b/go.sum @@ -5,6 +5,7 @@ github.com/gdamore/encoding v1.0.0 h1:+7OoQ1Bc6eTm5niUzBa0Ctsh6JbMW6Ra+YNuAtDBdk github.com/gdamore/encoding v1.0.0/go.mod h1:alR0ol34c49FCSBLjhosxzcPHQbf2trDkoo5dl+VrEg= github.com/gdamore/tcell v1.3.0 h1:r35w0JBADPZCVQijYebl6YMWWtHRqVEGt7kL2eBADRM= github.com/gdamore/tcell v1.3.0/go.mod h1:Hjvr+Ofd+gLglo7RYKxxnzCBmev3BzsS67MebKS4zMM= +github.com/go-sql-driver/mysql v1.4.0 h1:7LxgVwFb2hIQtMm87NdgAVfXjnt4OePseqT1tKx+opk= github.com/go-sql-driver/mysql v1.4.0/go.mod h1:zAC/RDZ24gD3HViQzih4MyKcchzm+sOG5ZlKdlhCg5w= github.com/gorilla/securecookie v1.1.1/go.mod h1:ra0sb63/xPlUeL+yeDciTfxMRAA+MP+HVt/4epWDjd4= github.com/gorilla/sessions v1.2.0/go.mod h1:dk2InVEVJ0sfLlnXv9EAgkf6ecYs/i80K/zI+bUmuGM= @@ -33,6 +34,7 @@ github.com/lucasb-eyer/go-colorful v1.0.3/go.mod h1:R4dSotOR9KMtayYi1e77YzuveK+i github.com/mattn/go-runewidth v0.0.4/go.mod h1:LwmH8dsx7+W8Uxz3IHJYH5QSwggIsqBzpuz5H//U1FU= github.com/mattn/go-runewidth v0.0.8 h1:3tS41NlGYSmhhe/8fhGRzc+z3AYCw1Fe1WAyLuujKs0= github.com/mattn/go-runewidth v0.0.8/go.mod h1:H031xJmbD/WCDINGzjvQ9THkh0rPKHF+m2gUSrubnMI= +github.com/mattn/go-sqlite3 v1.9.0 h1:pDRiWfl+++eC2FEFRy6jXmQlvp4Yh3z1MJKg4UeYM/4= github.com/mattn/go-sqlite3 v1.9.0/go.mod h1:FPy6KqzDD04eiIsT53CuJW3U88zkxoIYsOqkbpncsNc= github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= github.com/rivo/tview v0.0.0-20200528200248-fe953220389f h1:tRx/LLIP2PSA7johw9xhf+6NUCLC4BbMhpGdm110MGI= diff --git a/plan.go b/plan.go deleted file mode 100644 index 89606d6..0000000 --- a/plan.go +++ /dev/null @@ -1,338 +0,0 @@ -package main - -const testPlan = ` -[ - { - "Plan": { - "Node Type": "Limit", - "Startup Cost": 60970.08, - "Total Cost": 60970.11, - "Plan Rows": 10, - "Plan Width": 468, - "Actual Startup Time": 1941.751, - "Actual Total Time": 1941.780, - "Actual Rows": 10, - "Actual Loops": 1, - "Output": ["a.id", "(COALESCE(a.display_name, a.alert_name))", "a.external_alert_id", "a.venue_code", "altt.code", "altt.name", "altst.name", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "(percenttotal((sum(s.total_followed)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_overridden)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_ignored)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_not_seen)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_unknown)), (sum(s.total_alerts))))", "(((sum(s.total_alerts)) * a.estimated_cost))"], - "Shared Hit Blocks": 260587, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000, - "Plans": [ - { - "Node Type": "Sort", - "Parent Relationship": "Outer", - "Startup Cost": 60970.08, - "Total Cost": 60977.40, - "Plan Rows": 2927, - "Plan Width": 468, - "Actual Startup Time": 1941.747, - "Actual Total Time": 1941.757, - "Actual Rows": 10, - "Actual Loops": 1, - "Output": ["a.id", "(COALESCE(a.display_name, a.alert_name))", "a.external_alert_id", "a.venue_code", "altt.code", "altt.name", "altst.name", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "(percenttotal((sum(s.total_followed)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_overridden)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_ignored)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_not_seen)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_unknown)), (sum(s.total_alerts))))", "(((sum(s.total_alerts)) * a.estimated_cost))"], - "Sort Key": ["(sum(s.total_alerts))"], - "Sort Method": "top-N heapsort", - "Sort Space Used": 27, - "Sort Space Type": "Memory", - "Shared Hit Blocks": 260587, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000, - "Plans": [ - { - "Node Type": "Nested Loop", - "Parent Relationship": "Outer", - "Join Type": "Left", - "Startup Cost": 37552.36, - "Total Cost": 60906.83, - "Plan Rows": 2927, - "Plan Width": 468, - "Actual Startup Time": 522.250, - "Actual Total Time": 1865.979, - "Actual Rows": 32352, - "Actual Loops": 1, - "Output": ["a.id", "COALESCE(a.display_name, a.alert_name)", "a.external_alert_id", "a.venue_code", "altt.code", "altt.name", "altst.name", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "percenttotal((sum(s.total_followed)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_overridden)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_ignored)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_not_seen)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_unknown)), (sum(s.total_alerts)))", "((sum(s.total_alerts)) * a.estimated_cost)"], - "Shared Hit Blocks": 260587, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000, - "Plans": [ - { - "Node Type": "Nested Loop", - "Parent Relationship": "Outer", - "Join Type": "Left", - "Startup Cost": 37552.22, - "Total Cost": 56750.41, - "Plan Rows": 2927, - "Plan Width": 461, - "Actual Startup Time": 522.141, - "Actual Total Time": 1161.187, - "Actual Rows": 32352, - "Actual Loops": 1, - "Output": ["(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "a.id", "a.display_name", "a.alert_name", "a.external_alert_id", "a.venue_code", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "a.subtype_id", "altt.code", "altt.name"], - "Shared Hit Blocks": 197115, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000, - "Plans": [ - { - "Node Type": "Nested Loop", - "Parent Relationship": "Outer", - "Join Type": "Inner", - "Startup Cost": 37552.07, - "Total Cost": 56230.56, - "Plan Rows": 2927, - "Plan Width": 405, - "Actual Startup Time": 522.131, - "Actual Total Time": 918.895, - "Actual Rows": 32352, - "Actual Loops": 1, - "Output": ["(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "a.id", "a.display_name", "a.alert_name", "a.external_alert_id", "a.venue_code", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "a.type_id", "a.subtype_id"], - "Shared Hit Blocks": 132411, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000, - "Plans": [ - { - "Node Type": "Aggregate", - "Strategy": "Hashed", - "Parent Relationship": "Outer", - "Startup Cost": 37551.64, - "Total Cost": 37624.82, - "Plan Rows": 2927, - "Plan Width": 56, - "Actual Startup Time": 522.107, - "Actual Total Time": 587.536, - "Actual Rows": 32352, - "Actual Loops": 1, - "Output": ["s.alert_id", "sum(s.total_alerts)", "sum(s.total_followed)", "sum(s.total_overridden)", "sum(s.total_ignored)", "sum(s.total_not_seen)", "sum(s.total_unknown)"], - "Group Key": ["s.alert_id"], - "Shared Hit Blocks": 2784, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000, - "Plans": [ - { - "Node Type": "Bitmap Heap Scan", - "Parent Relationship": "Outer", - "Relation Name": "alert_daily_summaries", - "Schema": "analytics", - "Alias": "s", - "Startup Cost": 3265.35, - "Total Cost": 35524.50, - "Plan Rows": 115837, - "Plan Width": 56, - "Actual Startup Time": 10.745, - "Actual Total Time": 165.318, - "Actual Rows": 140451, - "Actual Loops": 1, - "Output": ["s.id", "s.org_id", "s.alert_id", "s.alert_day", "s.total_alerts", "s.total_followed", "s.total_overridden", "s.total_ignored", "s.total_not_seen", "s.total_unknown", "s.override_comments"], - "Recheck Cond": "((s.org_id = 2) AND (s.alert_day >= '2015-10-01 00:00:00'::timestamp without time zone) AND (s.alert_day <= '2015-12-01 00:00:00'::timestamp without time zone))", - "Rows Removed by Index Recheck": 0, - "Exact Heap Blocks": 2243, - "Lossy Heap Blocks": 0, - "Shared Hit Blocks": 2784, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000, - "Plans": [ - { - "Node Type": "Bitmap Index Scan", - "Parent Relationship": "Outer", - "Index Name": "alert_daily_summaries_org_id_alert_day_idx", - "Startup Cost": 0.00, - "Total Cost": 3236.39, - "Plan Rows": 115837, - "Plan Width": 0, - "Actual Startup Time": 10.445, - "Actual Total Time": 10.445, - "Actual Rows": 140451, - "Actual Loops": 1, - "Index Cond": "((s.org_id = 2) AND (s.alert_day >= '2015-10-01 00:00:00'::timestamp without time zone) AND (s.alert_day <= '2015-12-01 00:00:00'::timestamp without time zone))", - "Shared Hit Blocks": 541, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000 - } - ] - } - ] - }, - { - "Node Type": "Index Scan", - "Parent Relationship": "Inner", - "Scan Direction": "Forward", - "Index Name": "alerts_pkey1", - "Relation Name": "alerts", - "Schema": "analytics", - "Alias": "a", - "Startup Cost": 0.42, - "Total Cost": 6.34, - "Plan Rows": 1, - "Plan Width": 213, - "Actual Startup Time": 0.004, - "Actual Total Time": 0.005, - "Actual Rows": 1, - "Actual Loops": 32352, - "Output": ["a.id", "a.org_id", "a.external_alert_id", "a.alert_name", "a.display_name", "a.estimated_cost", "a.description", "a.source", "a.venue_code", "a.action_expected", "a.cancel_expected", "a.responsible_provider_type", "a.status", "a.version", "a.created_by", "a.created_date", "a.modified_by", "a.modified_date", "a.tracking_id", "a.record_date", "a.released_date", "a.released", "a.comments", "a.default_lockout_hours", "a.deploy_mode", "a.stop_type", "a.importance_level", "a.type_id", "a.subtype_id"], - "Index Cond": "(a.id = s.alert_id)", - "Rows Removed by Index Recheck": 0, - "Shared Hit Blocks": 129627, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000 - } - ] - }, - { - "Node Type": "Index Scan", - "Parent Relationship": "Inner", - "Scan Direction": "Forward", - "Index Name": "alert_types_id_org_id_key", - "Relation Name": "alert_types", - "Schema": "analytics", - "Alias": "altt", - "Startup Cost": 0.15, - "Total Cost": 0.17, - "Plan Rows": 1, - "Plan Width": 72, - "Actual Startup Time": 0.002, - "Actual Total Time": 0.003, - "Actual Rows": 1, - "Actual Loops": 32352, - "Output": ["altt.id", "altt.version", "altt.code", "altt.name", "altt.org_id"], - "Index Cond": "(a.type_id = altt.id)", - "Rows Removed by Index Recheck": 0, - "Shared Hit Blocks": 64704, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000 - } - ] - }, - { - "Node Type": "Index Scan", - "Parent Relationship": "Inner", - "Scan Direction": "Forward", - "Index Name": "alert_subtypes_id_org_id_key", - "Relation Name": "alert_subtypes", - "Schema": "analytics", - "Alias": "altst", - "Startup Cost": 0.14, - "Total Cost": 0.16, - "Plan Rows": 1, - "Plan Width": 23, - "Actual Startup Time": 0.002, - "Actual Total Time": 0.003, - "Actual Rows": 1, - "Actual Loops": 32352, - "Output": ["altst.id", "altst.version", "altst.code", "altst.name", "altst.org_id"], - "Index Cond": "(a.subtype_id = altst.id)", - "Rows Removed by Index Recheck": 0, - "Shared Hit Blocks": 63472, - "Shared Read Blocks": 0, - "Shared Dirtied Blocks": 0, - "Shared Written Blocks": 0, - "Local Hit Blocks": 0, - "Local Read Blocks": 0, - "Local Dirtied Blocks": 0, - "Local Written Blocks": 0, - "Temp Read Blocks": 0, - "Temp Written Blocks": 0, - "I/O Read Time": 0.000, - "I/O Write Time": 0.000 - } - ] - } - ] - } - ] - }, - "Planning Time": 0.507, - "Triggers": [ - ], - "Execution Time": 1953.684 - } -] -` diff --git a/plan_view.go b/plan_view.go index 36b58c1..f2badfa 100644 --- a/plan_view.go +++ b/plan_view.go @@ -79,8 +79,29 @@ func (p *planView) SetPlan(as []*postgres.Explain) { } func buildNode(p *postgres.Plan) *tview.TreeNode { - dur := f2d(p.ActualTotalTime) - n := tview.NewTreeNode(fmt.Sprintf("%s (%s)", p.NodeType, dur.String())) + dur := p.EffectiveTotalTime() + var ( + badEstimate string + sortBy string + scanOn string + scanIndex string + ) + if p.IsBadEstimate() { + badEstimate = " [red](bad estimate)[white]" + } + if len(p.SortKey) > 0 { + sortBy = " by " + strings.Join(p.SortKey, ", ") + } + if p.RelationName != nil { + scanOn = " on " + *p.RelationName + } + if p.IndexName != nil { + scanIndex = " using " + *p.IndexName + } + + label := fmt.Sprintf("%s (%s)%s%s%s%s", p.NodeType, dur.String(), + badEstimate, sortBy, scanOn, scanIndex) + n := tview.NewTreeNode(label) n.SetSelectable(true) n.SetReference(p) for _, subplan := range p.Plans { @@ -287,8 +308,7 @@ func f2a(f float32) string { } func f2d(f float32) time.Duration { - return time.Duration(int(f)) * time.Millisecond - // ms := time.Duration(int(f)) * time.Millisecond - // ns := time.Duration(int((f-float32(ms))*1000)) * time.Nanosecond - // return time.Duration(ms + ns) + // f is in milliseconds, multiply by 1k to get microseconds + // (thousandths of milliseconds) + return time.Duration(int(f)*1000) * time.Microsecond } diff --git a/postgres/plan.go b/postgres/plan.go new file mode 100644 index 0000000..9aa12f8 --- /dev/null +++ b/postgres/plan.go @@ -0,0 +1,338 @@ +package postgres + +const testPlan = ` +[ + { + "Plan": { + "Node Type": "Limit", + "Startup Cost": 60970.08, + "Total Cost": 60970.11, + "Plan Rows": 10, + "Plan Width": 468, + "Actual Startup Time": 1941.751, + "Actual Total Time": 1941.780, + "Actual Rows": 10, + "Actual Loops": 1, + "Output": ["a.id", "(COALESCE(a.display_name, a.alert_name))", "a.external_alert_id", "a.venue_code", "altt.code", "altt.name", "altst.name", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "(percenttotal((sum(s.total_followed)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_overridden)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_ignored)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_not_seen)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_unknown)), (sum(s.total_alerts))))", "(((sum(s.total_alerts)) * a.estimated_cost))"], + "Shared Hit Blocks": 260587, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000, + "Plans": [ + { + "Node Type": "Sort", + "Parent Relationship": "Outer", + "Startup Cost": 60970.08, + "Total Cost": 60977.40, + "Plan Rows": 2927, + "Plan Width": 468, + "Actual Startup Time": 1941.747, + "Actual Total Time": 1941.757, + "Actual Rows": 10, + "Actual Loops": 1, + "Output": ["a.id", "(COALESCE(a.display_name, a.alert_name))", "a.external_alert_id", "a.venue_code", "altt.code", "altt.name", "altst.name", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "(percenttotal((sum(s.total_followed)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_overridden)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_ignored)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_not_seen)), (sum(s.total_alerts))))", "(percenttotal((sum(s.total_unknown)), (sum(s.total_alerts))))", "(((sum(s.total_alerts)) * a.estimated_cost))"], + "Sort Key": ["(sum(s.total_alerts))"], + "Sort Method": "top-N heapsort", + "Sort Space Used": 27, + "Sort Space Type": "Memory", + "Shared Hit Blocks": 260587, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000, + "Plans": [ + { + "Node Type": "Nested Loop", + "Parent Relationship": "Outer", + "Join Type": "Left", + "Startup Cost": 37552.36, + "Total Cost": 60906.83, + "Plan Rows": 2927, + "Plan Width": 468, + "Actual Startup Time": 522.250, + "Actual Total Time": 1865.979, + "Actual Rows": 32352, + "Actual Loops": 1, + "Output": ["a.id", "COALESCE(a.display_name, a.alert_name)", "a.external_alert_id", "a.venue_code", "altt.code", "altt.name", "altst.name", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "percenttotal((sum(s.total_followed)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_overridden)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_ignored)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_not_seen)), (sum(s.total_alerts)))", "percenttotal((sum(s.total_unknown)), (sum(s.total_alerts)))", "((sum(s.total_alerts)) * a.estimated_cost)"], + "Shared Hit Blocks": 260587, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000, + "Plans": [ + { + "Node Type": "Nested Loop", + "Parent Relationship": "Outer", + "Join Type": "Left", + "Startup Cost": 37552.22, + "Total Cost": 56750.41, + "Plan Rows": 2927, + "Plan Width": 461, + "Actual Startup Time": 522.141, + "Actual Total Time": 1161.187, + "Actual Rows": 32352, + "Actual Loops": 1, + "Output": ["(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "a.id", "a.display_name", "a.alert_name", "a.external_alert_id", "a.venue_code", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "a.subtype_id", "altt.code", "altt.name"], + "Shared Hit Blocks": 197115, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000, + "Plans": [ + { + "Node Type": "Nested Loop", + "Parent Relationship": "Outer", + "Join Type": "Inner", + "Startup Cost": 37552.07, + "Total Cost": 56230.56, + "Plan Rows": 2927, + "Plan Width": 405, + "Actual Startup Time": 522.131, + "Actual Total Time": 918.895, + "Actual Rows": 32352, + "Actual Loops": 1, + "Output": ["(sum(s.total_alerts))", "(sum(s.total_followed))", "(sum(s.total_overridden))", "(sum(s.total_ignored))", "(sum(s.total_not_seen))", "(sum(s.total_unknown))", "a.id", "a.display_name", "a.alert_name", "a.external_alert_id", "a.venue_code", "a.stop_type", "a.deploy_mode", "a.estimated_cost", "a.type_id", "a.subtype_id"], + "Shared Hit Blocks": 132411, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000, + "Plans": [ + { + "Node Type": "Aggregate", + "Strategy": "Hashed", + "Parent Relationship": "Outer", + "Startup Cost": 37551.64, + "Total Cost": 37624.82, + "Plan Rows": 2927, + "Plan Width": 56, + "Actual Startup Time": 522.107, + "Actual Total Time": 587.536, + "Actual Rows": 32352, + "Actual Loops": 1, + "Output": ["s.alert_id", "sum(s.total_alerts)", "sum(s.total_followed)", "sum(s.total_overridden)", "sum(s.total_ignored)", "sum(s.total_not_seen)", "sum(s.total_unknown)"], + "Group Key": ["s.alert_id"], + "Shared Hit Blocks": 2784, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000, + "Plans": [ + { + "Node Type": "Bitmap Heap Scan", + "Parent Relationship": "Outer", + "Relation Name": "alert_daily_summaries", + "Schema": "analytics", + "Alias": "s", + "Startup Cost": 3265.35, + "Total Cost": 35524.50, + "Plan Rows": 115837, + "Plan Width": 56, + "Actual Startup Time": 10.745, + "Actual Total Time": 165.318, + "Actual Rows": 140451, + "Actual Loops": 1, + "Output": ["s.id", "s.org_id", "s.alert_id", "s.alert_day", "s.total_alerts", "s.total_followed", "s.total_overridden", "s.total_ignored", "s.total_not_seen", "s.total_unknown", "s.override_comments"], + "Recheck Cond": "((s.org_id = 2) AND (s.alert_day >= '2015-10-01 00:00:00'::timestamp without time zone) AND (s.alert_day <= '2015-12-01 00:00:00'::timestamp without time zone))", + "Rows Removed by Index Recheck": 0, + "Exact Heap Blocks": 2243, + "Lossy Heap Blocks": 0, + "Shared Hit Blocks": 2784, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000, + "Plans": [ + { + "Node Type": "Bitmap Index Scan", + "Parent Relationship": "Outer", + "Index Name": "alert_daily_summaries_org_id_alert_day_idx", + "Startup Cost": 0.00, + "Total Cost": 3236.39, + "Plan Rows": 115837, + "Plan Width": 0, + "Actual Startup Time": 10.445, + "Actual Total Time": 10.445, + "Actual Rows": 140451, + "Actual Loops": 1, + "Index Cond": "((s.org_id = 2) AND (s.alert_day >= '2015-10-01 00:00:00'::timestamp without time zone) AND (s.alert_day <= '2015-12-01 00:00:00'::timestamp without time zone))", + "Shared Hit Blocks": 541, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000 + } + ] + } + ] + }, + { + "Node Type": "Index Scan", + "Parent Relationship": "Inner", + "Scan Direction": "Forward", + "Index Name": "alerts_pkey1", + "Relation Name": "alerts", + "Schema": "analytics", + "Alias": "a", + "Startup Cost": 0.42, + "Total Cost": 6.34, + "Plan Rows": 1, + "Plan Width": 213, + "Actual Startup Time": 0.004, + "Actual Total Time": 0.005, + "Actual Rows": 1, + "Actual Loops": 32352, + "Output": ["a.id", "a.org_id", "a.external_alert_id", "a.alert_name", "a.display_name", "a.estimated_cost", "a.description", "a.source", "a.venue_code", "a.action_expected", "a.cancel_expected", "a.responsible_provider_type", "a.status", "a.version", "a.created_by", "a.created_date", "a.modified_by", "a.modified_date", "a.tracking_id", "a.record_date", "a.released_date", "a.released", "a.comments", "a.default_lockout_hours", "a.deploy_mode", "a.stop_type", "a.importance_level", "a.type_id", "a.subtype_id"], + "Index Cond": "(a.id = s.alert_id)", + "Rows Removed by Index Recheck": 0, + "Shared Hit Blocks": 129627, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000 + } + ] + }, + { + "Node Type": "Index Scan", + "Parent Relationship": "Inner", + "Scan Direction": "Forward", + "Index Name": "alert_types_id_org_id_key", + "Relation Name": "alert_types", + "Schema": "analytics", + "Alias": "altt", + "Startup Cost": 0.15, + "Total Cost": 0.17, + "Plan Rows": 1, + "Plan Width": 72, + "Actual Startup Time": 0.002, + "Actual Total Time": 0.003, + "Actual Rows": 1, + "Actual Loops": 32352, + "Output": ["altt.id", "altt.version", "altt.code", "altt.name", "altt.org_id"], + "Index Cond": "(a.type_id = altt.id)", + "Rows Removed by Index Recheck": 0, + "Shared Hit Blocks": 64704, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000 + } + ] + }, + { + "Node Type": "Index Scan", + "Parent Relationship": "Inner", + "Scan Direction": "Forward", + "Index Name": "alert_subtypes_id_org_id_key", + "Relation Name": "alert_subtypes", + "Schema": "analytics", + "Alias": "altst", + "Startup Cost": 0.14, + "Total Cost": 0.16, + "Plan Rows": 1, + "Plan Width": 23, + "Actual Startup Time": 0.002, + "Actual Total Time": 0.003, + "Actual Rows": 1, + "Actual Loops": 32352, + "Output": ["altst.id", "altst.version", "altst.code", "altst.name", "altst.org_id"], + "Index Cond": "(a.subtype_id = altst.id)", + "Rows Removed by Index Recheck": 0, + "Shared Hit Blocks": 63472, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "I/O Read Time": 0.000, + "I/O Write Time": 0.000 + } + ] + } + ] + } + ] + }, + "Planning Time": 0.507, + "Triggers": [ + ], + "Execution Time": 1953.684 + } +] +` diff --git a/postgres/postgres.go b/postgres/postgres.go index 3a5221e..5333f8d 100644 --- a/postgres/postgres.go +++ b/postgres/postgres.go @@ -3,6 +3,8 @@ package postgres import ( "encoding/json" "fmt" + "math" + "time" "github.com/jmoiron/sqlx" _ "github.com/lib/pq" @@ -139,10 +141,39 @@ func (db *DB) Analyze(query string) ([]*Explain, error) { } var exp []*Explain - if err := json.Unmarshal([]byte(plan.Plan), &exp); err != nil { - // if err := json.Unmarshal([]byte(testPlan), &a); err != nil { + // if err := json.Unmarshal([]byte(plan.Plan), &exp); err != nil { + if err := json.Unmarshal([]byte(testPlan), &exp); err != nil { return nil, fmt.Errorf("could not unmarshal plan: %v", err) } return exp, nil } + +func (p *Plan) EffectiveTotalTime() time.Duration { + var childTime time.Duration + for _, p := range p.Plans { + childTime = childTime + f2d(p.ActualTotalTime) + } + return f2d(p.ActualTotalTime) - childTime +} + +func (p *Plan) EstimateWrongness() (string, int) { + plan, actual := float64(p.PlanRows), float64(p.ActualRows) + bigger, smaller := math.Max(plan, actual), math.Min(plan, actual) + ratio := int(bigger / smaller) + if plan > actual { + return "over", ratio + } + return "under", ratio +} + +func (p *Plan) IsBadEstimate() bool { + _, ratio := p.EstimateWrongness() + return ratio >= 100 +} + +func f2d(f float32) time.Duration { + // f is in milliseconds, multiply by 1k to get microseconds + // (thousandths of milliseconds) + return time.Duration(int(f)*1000) * time.Microsecond +} -- cgit v1.2.3