Performance audit: the neighbourhood-subtree pattern behind the slow public pages

[kimadactyl]

While making the Trans Dimension site fast (a tag-only site anchored to the four UK country neighbourhoods, whose subtree is ~13,000 rows) a single recurring cause turned up in four different shapes. This issue records the pattern, the method to find it, what is fixed, and what still needs doing.

The pattern

Slow public pages trace to the site or user neighbourhood subtree (owned_neighbourhood_ids), which expands to thousands of rows for a nationally-anchored site. It goes wrong four ways:

1. Interpolated as a literal IN (...) list (180KB of SQL). Fix: a subquery over the ancestry path.
2. Materialised as objects when only a handful are needed. Fix: load only what the result needs.
3. Rebuilt per item in a render loop. Fix: memoise on the shared request-scoped object.
4. Passed as a subquery the planner then hashes and seq-scans. Fix: hand it a literal id list.

How to find it

• Split view_runtime vs db_runtime vs framework, do not trust wall time alone.
• When a per-item component is slow, render the whole collection in one pass and strip parts. The cost is often a shared-object method called per item, not an N+1.
• AppSignal reporting "N+1: No" does not clear a page; per-item CPU (rebuilding data, polymorphic url_for, unmemoised lookups) matters as much.
• Measure against a production clone, never seeds. These only appear at 13,000-neighbourhood scale.

Fixed

• Fix slow site pages: pass the site's partner ids to the events query as a literal list #3511 events counts (literal partner ids, not a subquery).
• Fix slow partners page: match the neighbourhood subtree by subquery, not a 13k-id list #3512 partners base scope (ancestry subquery, not a 13k-id IN list).
• Fix slow events page: build the neighbourhood dropdown without loading the whole subtree #3513 events neighbourhood dropdown (load only event-bearing neighbourhoods).
• Fix slow partners page: memoise the site neighbourhood subtree, trim the card #3517 partner card (memoise Site#owned_neighbourhood_ids, rebuilt per card).
• Memoise User neighbourhood subtree lookups #3518 memoise User#owned_neighbourhood_ids (admin policies, per partner).

Still to do, roughly by production traffic x mean

• PartnersController#show: 287ms mean across 11M hits on production (the largest aggregate). On the staging clone it is ~240ms of real work, 21 queries, no single landmine; needs a deeper profiling pass (events-by-organiser-or-place, map markers, containing sites).
• Article.for_site / news scoping: ~550ms on the staging clone, feeding both the homepage latest-article and NewsController#index (338ms mean on production). Likely the next-biggest single win.
• The Trans Dimension theme homepage: its events call and the article fetch dominate its ~600ms; revisit once Article.for_site is addressed.

Production AppSignal snapshot (before these fixes reach prod)

PartnersController#index 782ms mean, EventsController#index 368ms, PartnersController#show 287ms (11M hits), NewsController#index 338ms, SitesController#index 174ms. The first two are addressed by the PRs above once released.

[kimadactyl]

Progress update.

Round of fixes for the neighbourhood-subtree pattern, all merged or in flight:

• Fix slow site pages: pass the site's partner ids to the events query as a literal list #3511 events counts (literal partner ids)
• Fix slow partners page: match the neighbourhood subtree by subquery, not a 13k-id list #3512 partners base scope (ancestry subquery)
• Fix slow events page: build the neighbourhood dropdown without loading the whole subtree #3513 events neighbourhood dropdown (only event-bearing neighbourhoods)
• Fix slow partners page: memoise the site neighbourhood subtree, trim the card #3517 partner card (memoise Site#owned_neighbourhood_ids)
• Memoise User neighbourhood subtree lookups #3518 memoise User#owned_neighbourhood_ids
• Cache the filter facet counts, share the site query with the filter #3520 filter facet counts cached with a short TTL, filters share the controller query
• Article.for_site: match the neighbourhood subtree by subquery #3521 Article.for_site now an ancestry subquery (was ~550ms on home and news)
• Site#show_neighbourhoods?: cheap children check, not the 13k-id subtree #3522 Site#show_neighbourhoods? and join_word answer from a cheap children check, not the 13k-id build

Every hot caller of owned_neighbourhood_ids / owned_neighbourhoods is now converted or bypassed.

Staging timings, warm, Trans Dimension, on the 2-core box:

• events 3.6-5.7s -> ~0.7s
• partners 1.9s -> ~0.7s (further drop expected once Cache the filter facet counts, share the site query with the filter #3520/Site#show_neighbourhoods?: cheap children check, not the 13k-id subtree #3522 deploy)
• home ~1.2s (Article.for_site fix Article.for_site: match the neighbourhood subtree by subquery #3521 should help)

Remaining, diminishing returns:

• The directory firehose (placecal.org) computes its facets (categories_with_counts, partnerships_with_counts, neighbourhood_tree) live in PartnersController#render_directory_index and is not covered by the Cache the filter facet counts, share the site query with the filter #3520 caching, which only touched the per-site filters. High traffic, worth the same treatment.
• The Event card is ~6ms each of genuine Phlex rendering (a 195-line card with several view-context helper bridges). No shared-method shortcut; cutting it means simplifying the card, a design change.
• Index pages still run 35-39 queries for the base scope plus listing. Real work, no single hotspot.