Further reorg

2 files changed, 371 insertions, 2 deletions

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
+}