summaryrefslogtreecommitdiff
path: root/postgres
diff options
context:
space:
mode:
Diffstat (limited to 'postgres')
-rw-r--r--postgres/plan.go338
-rw-r--r--postgres/postgres.go35
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
+}
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-22 04:48:01 +0000