Module: Sixty::Plans

Defined in:
lib/sixty/plans.rb

Overview

A query plan, reduced to its shape.

── Why this is the signal worth having ───────────────────────────────────

Everything else this agent measures is a symptom: rows went up, latency went up, a method makes more queries than it did. A plan change is the cause — the moment a query stops using an index is the moment it becomes the incident, and every other number only reflects it afterwards, usually days afterwards, once the table is large enough for the difference to show.

It is also the finding a person can act on without understanding any of this. "This got slower" invites a shrug; "this stopped using index_orders_on_user_id" names the fix.

── The rules that make this safe to run in production ────────────────────

  1. EXPLAIN, never EXPLAIN ANALYZE. ANALYZE executes the statement to measure it — doubling the load of every explained query and, for anything that writes, performing the write a second time.
  2. GENERIC_PLAN, so no parameter is ever bound. Postgres 16 added it exactly for this. It is what keeps the privacy boundary intact by construction rather than by a promise to strip values afterwards.
  3. Read-only statements only, refused before they reach the database.
  4. Off the request path entirely. The Node agent issues its EXPLAIN after the caller's promise has settled; Ruby's notification subscriber runs inside the caller's stack with the connection still checked out, so doing the same here would put a second round trip in front of a user. Instead the statement is queued and the flush thread explains it later on a connection of its own. The plan lands one window late, which is nothing — a plan is a property of the statement and the schema, not of the call.

Constant Summary collapse

MAX_PLANS = 
500
MAX_PENDING = 
100
READ_ONLY =

Only statements that read. Anything else is refused here rather than relying on GENERIC_PLAN being side-effect free.

/\A\s*(select|with)\b/i.freeze
UNSAFE =

A statement already carrying its own EXPLAIN, or several statements at once, is not something to wrap in another EXPLAIN.

/\A\s*explain\b|;\s*\S/i.freeze
STRUCTURAL = 
[
  'Node Type', 'Join Type', 'Strategy', 'Relation Name',
  'Index Name', 'Scan Direction', 'Parent Relationship'
].freeze
MAX_DEPTH = 
12
MAX_NODES = 
120

Class Method Summary collapse

Class Method Details

.capture_pending(runner) ⇒ Object

Drain the queue, explaining each statement with the caller's runner.

Parameters:

  • runner (#call) —

    takes a SQL string, returns the parsed QUERY PLAN value (an Array or Hash), or raises. Supplied by the ActiveRecord instrumentation so this file never has to know what a connection is.



127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'lib/sixty/plans.rb', line 127

def capture_pending(runner)
  queued = mutex.synchronize do
    taken = pending
    @pending = {}
    taken
  end

  queued.each do |key, text|
    explained = runner.call("explain (generic_plan, format json) #{text}")
    shaped = shape(explained)
    next unless shaped

    set(key, shaped.merge(key: plan_key(shaped[:shape])))
  rescue StandardError
    # Swallowed, deliberately and completely. None of the ordinary reasons
    # this fails is the application's problem, and an observability agent
    # that turns a planning quirk into a runtime error has done far more
    # harm than the signal is worth.
    next
  end
end

.enqueue(text, key, dialect: :postgres) ⇒ Object

Remember a statement to explain on the next flush.

attempted is marked at enqueue rather than at success, so a statement the planner refuses — an older Postgres with no GENERIC_PLAN, an untypable parameter, a temp table that no longer exists — is tried once and then left alone instead of retried on every execution forever.

── Two refusals, both about values ──────────────────────────────────

MySQL gets no plans at all. It has no equivalent of GENERIC_PLAN: a statement can only be explained with its parameters bound, so capturing a plan would mean retaining somebody's query values in order to compose a command out of them. The signal is worth a great deal — an index that stopped being used names a cause rather than a symptom — and it is still not worth holding customer data to get. @sixty-sh/node refuses this for the same reason, and MongoDB is refused a third time on the same grounds.

A statement that arrived with literals in it is refused too, even on Postgres. where id = $1 carries no data and is safe to hand back; where id = 42 is what an application with prepared statements turned off produces, and explaining it would mean keeping the values in memory until the next flush. The lexer decides which is which, because the question is exactly "would normalization have removed anything".



109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/sixty/plans.rb', line 109

def enqueue(text, key, dialect: :postgres)
  return unless dialect == :postgres
  return unless text.is_a?(String) && READ_ONLY.match?(text) && !UNSAFE.match?(text)
  return unless Sql.value_free?(text)

  mutex.synchronize do
    return if attempted.key?(key) || pending.size >= MAX_PENDING || plans.size >= MAX_PLANS

    attempted[key] = true
    pending[key] = text
  end
end

.get(key) ⇒ Object 



70
71
72
 
# File 'lib/sixty/plans.rb', line 70

def get(key)
  mutex.synchronize { plans[key] }
end

.mutex ⇒ Object 



58
59
60
 
# File 'lib/sixty/plans.rb', line 58

def mutex
  @mutex ||= Mutex.new
end

.pending ⇒ Object 



66
67
68
 
# File 'lib/sixty/plans.rb', line 66

def pending
  @pending ||= {}
end

.plan_key(shape) ⇒ Object

A stable identity for a shape, so "did the plan change" is one string comparison. Key order is normalised on the way in: Postgres emits fields consistently today, but a shape whose identity depended on that would report a change the first time a minor release reordered them.



204
205
206
 
# File 'lib/sixty/plans.rb', line 204

def plan_key(shape)
  JSON.generate(canonical(shape))
end

.plans ⇒ Object 



62
63
64
 
# File 'lib/sixty/plans.rb', line 62

def plans
  @plans ||= {}
end

.reset! ⇒ Object 



218
219
220
221
222
223
224
 
# File 'lib/sixty/plans.rb', line 218

def reset!
  mutex.synchronize do
    @plans = {}
    @pending = {}
    @attempted = {}
  end
end

.sequential_scans(shape) ⇒ Object 



208
209
210
211
212
213
214
215
216
 
# File 'lib/sixty/plans.rb', line 208

def sequential_scans(shape)
  found = []
  walk = lambda do |node|
    found << node['Relation Name'] if node['Node Type'] == 'Seq Scan' && node['Relation Name']
    Array(node['Plans']).each { |child| walk.call(child) }
  end
  walk.call(shape) if shape
  found
end

.set(key, plan) ⇒ Object 



74
75
76
77
78
79
80
81
82
83
 
# File 'lib/sixty/plans.rb', line 74

def set(key, plan)
  mutex.synchronize do
    # Bounded like every other per-operation cache here. A process that
    # has seen five hundred distinct statements will not learn much from
    # the next one.
    return if plans.size >= MAX_PLANS || plans.key?(key)

    plans[key] = plan
  end
end

.shape(explain) ⇒ Object

Reduce an EXPLAIN (FORMAT JSON) result to a stable, comparable shape.

A plan as Postgres emits it is mostly numbers — startup cost, total cost, row estimate, width, loops — and every one of those moves whenever the statistics move, which is after every autovacuum. A detector comparing plans literally would fire constantly and mean nothing. What matters is structural and changes rarely: this query used to use an index and now scans the table.



157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'lib/sixty/plans.rb', line 157

def shape(explain)
  explain = JSON.parse(explain) if explain.is_a?(String)
  root = explain.is_a?(Array) ? explain.dig(0, 'Plan') : explain&.dig('Plan')
  return nil unless root

  nodes = 0
  scans = []

  walk = lambda do |node, depth|
    return nil if node.nil? || depth > MAX_DEPTH || nodes >= MAX_NODES

    nodes += 1
    out = {}
    STRUCTURAL.each { |field| out[field] = node[field] unless node[field].nil? }

    if node['Node Type'].is_a?(String) && node['Node Type'].include?('Scan')
      relation = node['Relation Name']
      index = node['Index Name']
      if relation
        scans << (index ? "#{node['Node Type']} #{relation} using #{index}" : "#{node['Node Type']} #{relation}")
      end
    end

    # The presence of a filter is structural; its contents are not.
    out['Filtered'] = true if node['Filter']
    out['IndexCond'] = true if node['Index Cond']
    out['HashCond'] = true if node['Hash Cond']

    children = Array(node['Plans']).map { |child| walk.call(child, depth + 1) }.compact
    out['Plans'] = children unless children.empty?
    out
  end

  shaped = walk.call(root, 0)
  return nil unless shaped

  # Two lists, because they answer different questions. `scans` is every
  # scan node, which is what a person reading a summary wants. `seqScans`
  # is only the sequential ones — the reads with no index — which is what
  # the detector reports as a defect.
  { shape: shaped, summary: summarize(shaped), scans: scans, seqScans: sequential_scans(shaped) }
end