Bug: Orgs and Locs + Job Templates are broken in 3.18 (probably 3.17 as well)

Support
15

6 hours in debugging with Claude. I think we’ve proven its not a SQL issue. It did end up patching a taxonomy initializer which signifincally reduced SQL query time. However, still no change in browser loading. About 15-25 seconds to load the job_templates page, even with this patch. Sharing for visibility:

Root cause

Taxonomix::ClassMethods#get_taxonomy_ids called by Authorizer#used_taxonomy_ids_for:

def get_taxonomy_ids(taxonomy, method)
  Array(taxonomy).map { |t| t.send(method) + t.ancestor_ids }.flatten.uniq
end

One subtree_ids + one ancestor_ids DB call per taxonomy per authorization check. With the current test user having 1,975 taxonomies × 2 types (orgs + locs) = ~3,960 identical ancestry-lookup queries per .authorized(...) call.

Patch

Foreman initializer /usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb that replaces get_taxonomy_ids with a single batched SQL query using ancestry LIKE predicates joined by OR. Cost is O(1) DB round-trips regardless of taxonomy count.

Full source:

$ cat /usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb
# /usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb
#
# Replaces Taxonomix::ClassMethods#get_taxonomy_ids with a batched version
# that resolves all taxonomy descendants in one SQL query instead of N.
#
# Original: loops over each taxonomy calling `t.subtree_ids + t.ancestor_ids`,
# producing 2N DB queries. With N=1,975 user-level taxonomies, that's ~3,950
# queries per .authorized(...) call -> 7-8 second page loads.
#
# Batched: one SELECT with an OR-chain of ancestry predicates, so cost is
# O(1) DB round-trips regardless of N.

Rails.application.config.to_prepare do
  Taxonomix::ClassMethods.module_eval do
    unless method_defined?(:get_taxonomy_ids_without_batch)
      alias_method :get_taxonomy_ids_without_batch, :get_taxonomy_ids
      def get_taxonomy_ids(taxonomy, method)
        arr = Array(taxonomy)
        return [] if arr.empty?
        ids = arr.map(&:id)
        where_clause = arr.map { "ancestry LIKE ? OR ancestry = ?" }.join(' OR ')
        bindings = arr.flat_map { |t| ["#{t.id}/%", t.id.to_s] }
        descendants = Taxonomy.where(where_clause, *bindings).pluck(:id)
        ancestors = arr.flat_map { |t| t.ancestry.to_s.split('/').map(&:to_i).reject(&:zero?) }
        (ids + descendants + ancestors).uniq
      end
    end
  end
end

Results (console, JobTemplate.authorized(:view_job_templates).to_a)

Unpatched Patched
Elapsed 7.99s 0.40s
Queries against taxonomies ~3,960 ~20
Returned template count 24 24 ✓

Results (production.log, GET /job_templates)

Metric Before After
Total 12,590 ms 1,477 ms
Views 4,929 ms 615 ms
ActiveRecord 3,782 ms 146 ms
Allocations 7,469,799 1,200,081

Now, to figure out still what is taking so long.