DRAFT: find new queries added in mr via auto explain

What does this MR do and why?

When running our unit test suite, we can use auto_explain on the database to capture information around what queries the database has processed.
The intention here is that we should be able to then run an MR and identify what new queries might have been introduced by the MR in question, and details about the query plan to be presented so reviews can analyze the impact of the change.

The intended output from this should be a script that can take a 2 paths, the 'master' auto_explain logs, and the auto_explain logs from the MR itself, and when run, it should be able to print a comment to the MR with details about new queries.

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Following diff might produce the subsequent output

commit 6f6cede707a15dac9ba5f8283c9c22ad2f087f7f
Author: John McDonnell <jmcdonnell@gitlab.com>
Date:   Wed Jan 29 18:41:53 2025 +0000

    DRAFT: Add a sample change

diff --git a/app/controllers/debug_controller.rb b/app/controllers/debug_controller.rb
new file mode 100644
index 000000000000..5de1e65a3840
--- /dev/null
+++ b/app/controllers/debug_controller.rb
@@ -0,0 +1,34 @@
+# frozen_string_literal: true
+
+# rubocop:disable Gitlab/NamespacedClass -- test change
+# rubocop:disable Gitlab/AvoidFeatureCategoryNotOwned -- test change
+# rubocop:disable CodeReuse/ActiveRecord -- test change
+class DebugController < ApplicationController
+  feature_category :not_owned
+  urgency :low
+
+  def bad_query
+    base_query = Project.where(
+      "CONCAT(projects.description::text, projects.name::text, projects.created_at::text) LIKE '%DEBUG_QUERY_8957%'"
+    ).joins("LEFT JOIN projects p2 ON p2.id = projects.id")
+
+    # Get the COUNT query and its fingerprint - this will match auto_explain
+    count_sql = base_query.select('COUNT(*)').to_sql
+
+    # Force transaction completion
+    ApplicationRecord.connection.execute('COMMIT')
+    sleep(20) # Give some time for logs to be written
+
+    render json: {
+      count: base_query.count,
+      base_sql: base_query.to_sql,
+      base_fingerprint: PgQuery.fingerprint(base_query.to_sql),
+      count_sql: count_sql,
+      normalized: PgQuery.normalize(count_sql),
+      count_fingerprint: PgQuery.fingerprint(count_sql)
+    }
+  end
+end
+# rubocop:enable Gitlab/NamespacedClass -- test change
+# rubocop:enable Gitlab/AvoidFeatureCategoryNotOwned -- test change
+# rubocop:enable CodeReuse/ActiveRecord -- test change
diff --git a/config/routes.rb b/config/routes.rb
index ba70a05a78f2..e50c7cffcca4 100644
--- a/config/routes.rb
+++ b/config/routes.rb
@@ -23,6 +23,10 @@
 
     draw :development
 
+    scope '-' do
+      get 'debug/bad_query', to: 'debug#bad_query', as: :debug_bad_query
+    end
+
     use_doorkeeper do
       controllers applications: 'oauth/applications',
         authorized_applications: 'oauth/authorized_applications',
diff --git a/spec/features/debug_spec.rb b/spec/features/debug_spec.rb
new file mode 100644
index 000000000000..3da318e18d4b
--- /dev/null
+++ b/spec/features/debug_spec.rb
@@ -0,0 +1,19 @@
+# frozen_string_literal: true
+
+require 'spec_helper'
+
+RSpec.describe 'Debug', :js, feature_category: :shared do
+  let_it_be(:user) { create(:user) }
+
+  before do
+    sign_in(user)
+  end
+
+  describe 'GET /-/debug/bad_query' do
+    it 'executes an inefficient query that should be caught by auto_explain' do
+      visit "/-/debug/bad_query"
+
+      expect(page).to have_content('56bd76354eb069e2')
+    end
+  end
+end

$ bundle exec ruby auto_explain_differ.rb ./auto_explain/rspec.25831.main.ndjson ./auto_explain/rspec.26662.main.ndjson

New queries in MR:

{"query":"SELECT COUNT(*) FROM \"projects\" LEFT JOIN projects p2 ON p2.id = projects.id WHERE (CONCAT(projects.description::text, projects.name::text, projects.updated_at::text) LIKE '%DEBUG_QUERY_7957%') /*application:test,correlation_id:1975cf84-f5f4-4c65-b837-d8ab64ab4eb5,endpoint_id:DebugController#bad_query,db_config_database:gitlabhq_test,db_config_name:main,line:/app/controllers/debug_controller.rb:19:in `bad_query'*/","plan":{"Plans":[{"Alias":"projects","Filter":"(concat(description, (name)::text, (updated_at)::text) ~~ '%DEBUG_QUERY_7957%'::text)","Node Type":"Seq Scan","Plan Rows":1,"Plan Width":0,"Total Cost":11.0,"Startup Cost":0.0,"Async Capable":false,"Relation Name":"projects","Parallel Aware":false,"Parent Relationship":"Outer"}],"Strategy":"Plain","Node Type":"Aggregate","Plan Rows":1,"Plan Width":8,"Total Cost":11.01,"Partial Mode":"Simple","Startup Cost":11.0,"Async Capable":false,"Parallel Aware":false},"fingerprint":"ee324fdb4d4a91dd","normalized":"SELECT COUNT(*) FROM \"projects\" LEFT JOIN projects p2 ON p2.id = projects.id WHERE (CONCAT(projects.description::text, projects.name::text, projects.updated_at::text) LIKE $1) /*application:test,correlation_id:1975cf84-f5f4-4c65-b837-d8ab64ab4eb5,endpoint_id:DebugController#bad_query,db_config_database:gitlabhq_test,db_config_name:main,line:/app/controllers/debug_controller.rb:19:in `bad_query'*/"}

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.