Endpoint trace with SQL and EXPLAIN ANALYZE

Endpoint: GET /api/v1/events_sessions?timeperiod=upcoming&per_page=100&page_index=1&scope=all&status=all

Account: 0010X00004o0sOBQAY (Adil Nyambasha, Forum Staff Member)

Generated locally on 2026-05-06. The local trace follows the same endpoint described in the previous research gist. Redis was treated as cold/miss. Salesforce-backed calls are shown as SOQL and cannot be EXPLAIN ANALYZEd against the local Postgres database; local SQL/ActiveRecord/Postgres datasource calls were run with EXPLAIN (ANALYZE, BUFFERS, VERBOSE).

Summary

Route is Grape: GET /api/:version/events_sessions(.json).
type is omitted, so TYPE_ALL makes MixedListingService fetch sessions first and events second.
The account is a forum-staff account, so event filters use the forum-staff branch and event listing calls Repositories::Events#find_publishables.
Cold trace shape: 50 backing calls: 24 SQL/AR/Postgres and 26 SOQL calls.
Response shape in the traced run: 83 items = 70 community sessions + 13 events.
Local SQL calls with the highest measured times:
- Query 4, current PEM delegates CTE: 135.267 ms
- Query 5, session PEM accounts: 134.484 ms
- Query 10, role session ids: 43.563 ms
- Query 12, session team-member session ids: 29.303 ms
- Query 6, final managed accounts: 20.836 ms

Entrypoint and control flow

Route/controller

lib/eventws/controllers/events.rb:131-159

def fetch_events_and_sessions
  filters = build_event_listing_filters(params)
  pagination_model = use_pagination_per_id? ? page : page_by_page_index
  all = mixed_listing_service.fetch_data(current_user, filters, pagination_model)
  return all, pagination_model
end

resource :events_sessions do
  get do
    all, pagination_model = fetch_events_and_sessions
    link_header(pagination_model)
    session_service = Services::Session::SessionService.new
    present all,
            with: EventWS::Presenters::MixedListing,
            languages: prefered_languages,
            current_user: current_user,
            user_session: current_session,
            session_service: session_service
  end
end

Filter construction

lib/eventws/helpers/events_sessions.rb:46-132

position = EventWS::Repositories::Positions.new.find_operational_contact_position(current_user.salesforce_id)

elsif current_user.forum_staff?
  event_filter = EventWS::Models::Utils::EventListingFilters.new(
    account_id,
    with_delegates_ids,
    params[:date],
    STATUS_PARAM_TO_FILTER[params[:status]],
    TIMEPERIOD_PARAM_TO_FILTER[params[:timeperiod]],
    SCOPE_PARAM_TO_FILTER[params[:scope]],
    ids,
    true,
    TYPE_PARAM_TO_FILTER[params[:type]],
    params[:item_format],
    params[:event_series],
    params[:year],
    position&.operational_contact?,
    current_user.sf_user_id
  )
end

Mixed listing dispatch

lib/eventws/services/mixed_listing_service.rb:13-28

def fetch_data(current_user, event_listing_filters, pagination_model)
  community_sessions = if fetch_sessions?(event_listing_filters)
                         fetch_sessions(current_user, event_listing_filters)
                       else
                         []
                       end

  events = if fetch_events?(event_listing_filters)
             fetch_events(current_user, event_listing_filters, pagination_model)
           else
             []
           end

  result = sort_listing(community_sessions + events, event_listing_filters, pagination_model)
  paginate_listing(result, event_listing_filters, pagination_model)
end

lib/eventws/services/mixed_listing_service.rb:95-120

def fetch_events(current_user, event_listing_filters, pagination_model)
  events = []
  current_page = pagination_model.clone
  current_page.current_page_index = 1 if current_page.is_a?(EventWS::Models::Utils::PagePerPageIndex)
  max_expected_items_to_paginate = calculate_max_items_to_paginate(pagination_model)

  loop do
    events_subresult = event_listing_service.fetch_event_listing(current_user, event_listing_filters, current_page).to_a
    events.concat(events_subresult) if events_subresult.any?
    break if events_subresult.empty? || events.size >= max_expected_items_to_paginate

    if current_page.is_a?(EventWS::Models::Utils::PagePerPageIndex)
      current_page.current_page_index += 1
    else
      current_page.cursor = events_subresult.last&.id
    end
  end

  events
end

Query sequence

Placeholders used below:

:account_id = '0010X00004o0sOBQAY'
:sf_user_id = '0050X000009mt8mQAA'
:today = '2026-05-06'
:previous_january = '2025-01-01'

Large generated IN (...) lists are abbreviated as :managed_account_ids, :account_ids, :session_ids, or :event_ids. In the local run those lists were generated before EXPLAIN ANALYZE and passed as literal values.

1. Operational-contact position for filter construction

Why: build_event_listing_filters needs to know whether the forum-staff user is also an operational contact.

Code: lib/eventws/helpers/events_sessions.rb:56; SQL source lib/eventws/datasources/postgres/positions.rb:6-35.

SELECT user_account.sfid AS account_id, user_account.name AS account_name,
       primary_position.sfid AS position_id, primary_position.Organization__c AS organization,
       primary_position.Top_Level_Organization__c AS top_level_organization,
       array_remove(array_agg(oc_roles.type__c), NULL) AS oc_types,
       array_remove(array_agg(oc_roles.event__c || '-' || oc_roles.organization__c), NULL) AS events_oc,
       array_remove(array_agg(oc_roles.constituent_account__c), NULL) AS poc_account_ids
FROM account AS user_account
LEFT JOIN position__c AS primary_position ON primary_position.personname__c = user_account.sfid
LEFT JOIN operational_contact__c AS oc_roles
  ON oc_roles.Operational_Contact__c = user_account.sfid AND oc_roles.status__c = 'Active'
WHERE user_account.sfid = '0010X00004o0sOBQAY'
  AND primary_position.PrimaryPosition__c = TRUE
GROUP BY user_account.sfid, user_account.name, primary_position.sfid,
         primary_position.Organization__c, primary_position.Top_Level_Organization__c
LIMIT 1;

EXPLAIN ANALYZE: 0.156 ms. Uses hcu_idx_account_sfid, index_position__c_on_personname__c_and_primaryposition__c, and index_operational_contact_on_contact_and_status. No concern.

2. Forum-staff record load

Why: The forum-staff login-delegator path loads the ActiveRecord ForumStaffMember so it can call managed_accounts.

Code: lib/eventws/repositories/login_delegators.rb:169-183; app/models/forum_staff_member/pem.rb:4-10.

SELECT account.*
FROM account
WHERE account.recordtypeid = '012b00000000ARMAA2'
  AND account.toplink_account_status_text__c IN ('Active', 'Provisioned')
  AND account.ispersonaccount = TRUE
  AND account.sfid = '0010X00004o0sOBQAY'
LIMIT 1;

EXPLAIN ANALYZE: 0.021 ms. Uses hcu_idx_account_sfid. No concern.

3. Published events since previous January

Why: current_pem_delegates limits PEM delegation paths to published events since the previous January.

Code: app/models/forum_staff_member/pem.rb:25-26.

SELECT event__c.*
FROM event__c
WHERE event__c.toplink_status_final_text__c != 'None'
  AND event__c.status__c = 'Open'
  AND event__c.start_date__c >= '2025-01-01';

EXPLAIN ANALYZE: 0.380 ms. Uses existing event date/status index. No concern.

4. Current PEM delegates CTE

Why: Computes participants managed by this PEM through opportunity, registration, parent-registration, secondary-engagement-manager, and accompanying paths.

Code: app/models/forum_staff_member/pem.rb:12-66.

def current_pem_delegates
  pem_delegates(event: Event.published.since_previous_january)
end

def pem_delegates(event: nil)
  Account
    .participants
    .with(candidate_account_ids: candidate_account_ids_for(event))
    .joins("INNER JOIN candidate_account_ids ON candidate_account_ids.sfid = account.sfid")
    .excluding(self)
    .distinct
end

SQL shape:

WITH candidate_account_ids AS (
  SELECT account.sfid FROM account JOIN opportunity ... WHERE account.tech_salesforceuser__c = '0050X000009mt8mQAA' ...
  UNION ALL
  SELECT account.sfid FROM account JOIN opportunity ... WHERE opportunity.participant_pem__c = '0050X000009mt8mQAA' ...
  UNION ALL
  SELECT account.sfid FROM account JOIN opportunity ... WHERE registration__c.eventpemuser__c = '0050X000009mt8mQAA' ...
  UNION ALL
  SELECT account.sfid FROM account JOIN opportunity ... WHERE parent_registration.eventpemuser__c = '0050X000009mt8mQAA' ...
  UNION ALL
  SELECT account.sfid FROM account JOIN opportunity ... WHERE parent_account.ownerid = '0050X000009mt8mQAA' ...
  UNION ALL
  SELECT account.sfid FROM account JOIN opportunity ... WHERE account.secondary_engagement_manager__c = '0050X000009mt8mQAA' ...
  UNION ALL
  SELECT account.sfid FROM account JOIN opportunity ... WHERE accompanying parent participant = '0010X00004o0sOBQAY' ...
)
SELECT DISTINCT account.*
FROM account
INNER JOIN recordtype record_type ON record_type.sfid = account.recordtypeid
INNER JOIN candidate_account_ids ON candidate_account_ids.sfid = account.sfid
WHERE account.toplink_account_status_text__c IN ('Active', 'Provisioned')
  AND account.ispersonaccount = TRUE
  AND record_type.developername IN ('Forum_Staff_Member', 'Constituent', 'Other')
  AND account.sfid != '0010X00004o0sOBQAY';

EXPLAIN ANALYZE: 135.267 ms. The plan uses a Parallel Append across the seven UNION arms, many index scans, and a final Unique. The slowest observed work is in opportunity/registration/account delegation arms; no single missing-index sequential scan dominates. This is a naturally expensive query because it intentionally unions many delegation paths.

5. Session PEM accounts

Why: Adds community-session participants managed by this PEM, even if they were not found through event opportunities/registrations.

Code: app/models/forum_staff_member/pem.rb:35-44.

community_session_participants = Role
  .joins(session: [ :record_type, :session_logistic ])
  .merge(Session.open)
  .merge(SessionLogistic.since_previous_january)
  .merge(RecordType.community_session)
  .select(:constituent_id)

Account.participants.managed_by(salesforce_user_id).where(id: community_session_participants).excluding(self)

SQL shape:

SELECT account.*
FROM account
INNER JOIN recordtype record_type ON record_type.sfid = account.recordtypeid
WHERE account.toplink_account_status_text__c IN ('Active', 'Provisioned')
  AND account.ispersonaccount = TRUE
  AND (account.ownerid = '0050X000009mt8mQAA' OR account.secondary_engagement_manager__c = '0050X000009mt8mQAA')
  AND account.sfid IN (
    SELECT role__c.constituent__c
    FROM role__c
    INNER JOIN session__c ON session__c.sfid = role__c.session__c
    INNER JOIN recordtype ON recordtype.sfid = session__c.recordtypeid
    INNER JOIN session_logistics__c ON session_logistics__c.session__c = session__c.sfid
    WHERE session__c.status__c = 'Open'
      AND session_logistics__c.sessionstartdate__c >= '2025-01-01'
      AND recordtype.developername = 'Community_Session'
  )
  AND account.sfid != '0010X00004o0sOBQAY';

EXPLAIN ANALYZE: 134.484 ms. The expensive part is the subquery hash with ~243,950 role rows after joining open community sessions and logistics. This can be sensitive to role__c volume.

6. Final managed accounts

Why: Combines current PEM delegates and session PEM accounts, excludes self, and orders by name.

Code: app/models/forum_staff_member/pem.rb:4-10.

combined_ids = current_pem_delegates.ids | session_pem_accounts.ids
Account.where(id: combined_ids).excluding(self).order(:sort_by_name)

SELECT account.*
FROM account
WHERE account.toplink_account_status_text__c IN ('Active', 'Provisioned')
  AND account.ispersonaccount = TRUE
  AND account.sfid IN (:managed_account_ids)
  AND account.sfid != '0010X00004o0sOBQAY'
ORDER BY account.sort_by_name__c ASC;

EXPLAIN ANALYZE: 20.836 ms. Uses bitmap scans over hcu_idx_account_sfid and hc_idx_account_toplink_account_status_text__c, then sorts 663 rows. Acceptable, but repeated calls multiply the cost.

7. User community memberships

Why: Community-session lookup needs the user's active communities/projects.

Code: lib/eventws/datasources/postgres/users.rb:94-108.

SELECT membership.community__c, user_community.forumnetwork__c
FROM membership__c AS membership
LEFT JOIN forumcommunity__c AS user_community ON membership.community__c = user_community.sfid
WHERE membername__c = '0010X00004o0sOBQAY'
  AND member_status__c = 'Active'
  AND user_community.toplink_status_final_text__c IN ('All in Public','All Toplink','Unlisted','Private','All in Network')
  AND user_community.communitystatus__c IN ('Active','06 - Activated');

EXPLAIN ANALYZE: 0.058 ms. Uses hc_idx_membership__c_membername__c and hcu_idx_forumcommunity__c_sfid. No concern.

8. Active communities by membership ids

Why: The code validates active community ids before using team-member community memberships. In this account's run the input list was empty.

Code: lib/eventws/services/open_community_session_service.rb:9-32 and user/community datasource calls.

SELECT sfid
FROM forumcommunity__c
WHERE sfid IN (:community_ids)
  AND communitystatus__c IN ('Active','06 - Activated')
  AND toplink_status_final_text__c IN ('All in Public','All Toplink','Unlisted','Private','All in Network');

EXPLAIN ANALYZE: not executed with a non-empty list in this trace. The generated empty-list query returned no rows; with ids it should use hcu_idx_forumcommunity__c_sfid.

9. Forum-staff team-member communities

Why: Adds communities where the forum-staff user is an active community team member.

Code: lib/eventws/datasources/postgres/users.rb.

SELECT forum_community_network__c
FROM community_team_member__c
WHERE forum_staff_member__c = '0010X00004o0sOBQAY'
  AND status__c = 'Active';

EXPLAIN ANALYZE: 0.040 ms. Uses an index; no concern.

10. Community sessions where self/managed accounts have roles

Why: Finds community-session ids where the account or managed accounts have a participant/reserve-seat role.

Code: lib/eventws/datasources/postgres/sessions.rb:2083-2114.

def query_roles_sessions_ids(account_ids, filters)
  <<~SQL.squish
    SELECT user_role.session__c as session_id
    FROM role__c AS user_role
    INNER JOIN recordtype AS role_rt ON (role_rt.sfid = user_role.recordtypeid)
    INNER JOIN session__c AS community_session ON (community_session.sfid = user_role.session__c)
    INNER JOIN recordtype AS session_rt ON (session_rt.sfid = community_session.recordtypeid)
    INNER JOIN session_logistics__c AS session_log ON (session_log.session__c = user_role.session__c)
    WHERE ...
  SQL
end

SELECT user_role.session__c AS session_id
FROM role__c AS user_role
INNER JOIN recordtype AS role_rt ON role_rt.sfid = user_role.recordtypeid
INNER JOIN session__c AS community_session ON community_session.sfid = user_role.session__c
INNER JOIN recordtype AS session_rt ON session_rt.sfid = community_session.recordtypeid
INNER JOIN session_logistics__c AS session_log ON session_log.session__c = user_role.session__c
WHERE ((role_rt.DeveloperName = 'Participant' AND user_role.Type__c = 'Reserve a Seat') OR role_rt.DeveloperName = 'Role')
  AND community_session.Status__c = 'Open'
  AND community_session.Toplink_Status_Final_Text__c IN ('All in Public','All Toplink','All in Toplink','All in Session','All in Community')
  AND user_role.Constituent__c IN (:account_ids)
  AND session_rt.DeveloperName = 'Community_Session'
  AND session_log.SessionEndDate__c IS NOT NULL
  AND session_log.SessionStartDate__c IS NOT NULL
  AND session_log.SessionEndTime__c IS NOT NULL
  AND session_log.SessionStartTime__c IS NOT NULL
  AND session_log.TECH_EndDateTimeUTC__c >= :now_utc;

EXPLAIN ANALYZE: 43.563 ms. Plan starts from open community sessions (5,931 rows), probes session logistics, then probes roles by hc_idx_role__c_session__c; each role probe removes rows by constituent filter. Potential composite index candidate on role__c.

11. Community/project sessions open to matching communities

Why: Finds open community sessions reachable through community/project membership. For this account, the membership inputs were empty.

Code: lib/eventws/datasources/postgres/sessions.rb:2116-2135.

SELECT calendar_item.session__c AS session_id
FROM calendar_item__c AS calendar_item
INNER JOIN session__c AS community_session ON calendar_item.session__c = community_session.sfid
INNER JOIN recordtype AS session_rt ON session_rt.sfid = community_session.recordtypeid
INNER JOIN session_logistics__c AS session_log ON session_log.session__c = calendar_item.session__c
WHERE community_session.openregistration__c = TRUE
  AND session_rt.developername = 'Community_Session'
  AND calendar_item.toplink_status_final_text__c IN ('All in Community', 'All in Public')
  AND community_session.sfid NOT IN (:role_session_ids)
  AND (calendar_item.project__c IN (:project_ids) OR calendar_item.forum_community__c IN (:community_ids))
  AND session_log.TECH_EndDateTimeUTC__c >= :now_utc;

EXPLAIN ANALYZE: 0.004 ms in this trace because the input set was empty and the query was reduced to a false predicate. No conclusion for non-empty memberships.

12. Community sessions where account/delegates are session team members

Why: Forum staff can see sessions where they or managed accounts are active session team members.

Code: lib/eventws/datasources/postgres/sessions.rb:2043-2062.

SELECT session_team_member.session__c AS session_id
FROM session_Team_Member__c AS session_team_member
INNER JOIN session__c AS community_session ON community_session.sfid = session_team_member.session__c
INNER JOIN recordtype AS session_rt ON session_rt.sfid = community_session.recordtypeid
INNER JOIN session_logistics__c AS session_log ON session_log.session__c = community_session.sfid
WHERE session_team_member.Status__c = 'Active'
  AND session_team_member.Forum_Staff_Member__c IN (:account_ids)
  AND session_rt.DeveloperName = 'Community_Session'
  AND community_session.Status__c = 'Open'
  AND community_session.Toplink_Status_Final_Text__c IN ('All in Public','All Toplink','All in Toplink','All in Session','All in Community')
  AND session_log.TECH_EndDateTimeUTC__c >= :now_utc;

EXPLAIN ANALYZE: 29.303 ms. Similar to query 10, it starts from 5,931 open community sessions, filters logistics to 260 future sessions, then probes session_team_member__c by session and removes rows by forum-staff/status predicates. Potential composite index candidate on session_team_member__c.

13. Open TopLink community sessions

Why: Adds open-registration community sessions visible to all TopLink/public users.

Code: lib/eventws/datasources/postgres/sessions.rb:2064-2081.

SELECT community_session.sfid AS session_id
FROM session__c AS community_session
INNER JOIN session_logistics__c AS session_log ON session_log.session__c = community_session.sfid
INNER JOIN recordtype AS session_rt ON session_rt.sfid = community_session.recordtypeid
WHERE community_session.toplink_status_final_text__c IN ('All Toplink','All in Public')
  AND community_session.status__c = 'Open'
  AND session_rt.developername = 'Community_Session'
  AND community_session.openregistration__c = TRUE
  AND session_log.TECH_EndDateTimeUTC__c >= :now_utc;

EXPLAIN ANALYZE: 1.077 ms. No concern.

14. Roles for resolved account ids and session ids

Why: After session ids are resolved, roles are loaded for those sessions/accounts so session presentation can attach role/status data.

Code: lib/eventws/datasources/postgres/roles.rb:470-499.

SELECT user_role.*
FROM role__c AS user_role
WHERE user_role.constituent__c IN (:account_ids)
  AND user_role.session__c IN (:session_ids);

EXPLAIN ANALYZE: 7.114 ms. Uses bitmap-and of separate session__c and constituent__c indexes. A composite index can reduce bitmap work, but this query is not the dominant cost in the measured run.

15. Session payload for resolved session ids

Why: Loads full session data for the resolved community-session ids.

Code: lib/eventws/repositories/sessions.rb:316-324; SQL source lib/eventws/datasources/postgres/agenda.rb:249-353.

SELECT session__c.*
FROM session__c
WHERE session__c.sfid IN (:session_ids);

The real query in Agenda#find_agenda_sessions_by_ids selects many explicit session, programme, event, logistics, venue, city, country, and stream fields with joins.

EXPLAIN ANALYZE for key access: 0.861 ms. Uses sfid access. No concern for id lookup; the full projection cost depends on joined rows and selected width.

16. Session organisers for resolved sessions

Why: Enriches session payloads with session organisers and publishable community names.

Code: lib/eventws/datasources/postgres/agenda.rb:238-353.

SELECT session_organiser__c.*, forumcommunity__c.publishable_name__c
FROM session_organiser__c
LEFT JOIN forumcommunity__c ON forumcommunity__c.sfid = session_organiser__c.forum_community__c
WHERE session_organiser__c.session__c IN (:session_ids);

EXPLAIN ANALYZE: not separately captured in the simplified local script. Expected access path is by session_organiser__c.session__c if indexed, then forum community by sfid.

Event branch, iteration 1 (`page_index=1`)

17. Public publishable event ids

Why: Adds public/publishable event ids for the event listing branch.

Code: lib/eventws/repositories/events.rb:100; SQL source lib/eventws/datasources/postgres/events.rb:654-673.

SELECT event__c.sfid AS event_id
FROM event__c
LEFT JOIN topic_item__c ON topic_item__c.event__c = event__c.sfid AND topic_item__c.type__c = 'Event'
LEFT JOIN topic__c ON topic_item__c.topic__c = topic__c.sfid AND topic__c.recordtypeid = '012b0000000M4SAAA0'
LEFT JOIN programme__c AS event_programme ON event__c.forum_official_programme__c = event_programme.sfid
LEFT JOIN city__c ON city__c.sfid = event__c.city__c
LEFT JOIN country__c ON country__c.sfid = city__c.country__c
WHERE event__c.toplink_status_final_text__c = 'All Toplink'
  AND event__c.status__c IN ('Open', 'Activated')
  AND city__c.name NOT IN ('Unknown')
  AND country__c.name NOT IN ('Unknown')
  AND city__c.timezone__c <> ''
  AND event_programme.end_date__c >= '2026-05-06';

EXPLAIN ANALYZE: 2.440 ms. Uses event status/toplink index and indexed joins. programme__c is scanned (12,224 rows) to filter end date, but the total time is low. No immediate index need.

18. Open-registration event ids

Why: Adds events that are open-registration/forum-wide-audience candidates.

Code: lib/eventws/repositories/events.rb:113; SQL source lib/eventws/datasources/postgres/events.rb:675-698.

SELECT event__c.sfid AS event_id
FROM event__c
INNER JOIN programme__c AS event_programme ON event__c.forum_official_programme__c = event_programme.sfid
LEFT JOIN city__c ON city__c.sfid = event__c.city__c
LEFT JOIN country__c ON country__c.sfid = city__c.country__c
LEFT JOIN audience__c
  ON audience__c.event__c = event__c.sfid
 AND audience__c.recordtypeid = '0120X000000gjC4QAI'
 AND audience__c.status__c = 'Enabled'
WHERE event__c.toplink_status_final_text__c = 'All Toplink'
  AND (
    event__c.registration_audience__c IN ('Invitation/Nomination + Open Registration', 'Invitation Only + Open Registration', 'Open Registration only')
    OR audience__c.sfid IS NOT NULL
  )
  AND event__c.status__c IN ('Open','Activated')
  AND city__c.name NOT IN ('Unknown')
  AND country__c.name NOT IN ('Unknown')
  AND city__c.timezone__c <> ''
  AND event_programme.end_date__c >= '2026-05-06';

EXPLAIN ANALYZE: 1.254 ms. No concern.

19. Operational-contact position lookup repeated for event listing

Why: Repositories::Events#find_publishables_event_ids recomputes the current user's OC position for event-OC visibility.

Code: lib/eventws/repositories/events.rb:119.

SQL: same as query 1.

EXPLAIN ANALYZE: same shape as query 1; measured query 1 at 0.156 ms. The query is fast, but it is duplicated in the request.

20. Event-team-member event ids (SOQL)

Why: Forum staff can see events where they are active event team members.

Code: lib/eventws/repositories/events.rb:138; SOQL source lib/eventws/datasources/salesforce/events.rb:559-575.

SELECT Event__c FROM Event_Team_Member__c
WHERE Forum_Staff_Member__c = '0010X00004o0sOBQAY'
  AND Status__c = 'Active'
  AND Event__r.Forum_Official_Programme__r.Start_Date__c > 2025-01-01
  AND Event__r.Forum_Official_Programme__r.End_Date__c >= 2026-05-06
  AND Event__r.Toplink_Status_Final_Text__c != 'None';

EXPLAIN ANALYZE: not applicable; this is Salesforce SOQL.

21. Self opportunities for event listing (SOQL)

Why: Adds events where the current account has a relevant opportunity.

Code: lib/eventws/repositories/events.rb:339-346; SOQL source lib/eventws/datasources/salesforce/events.rb:701-786.

SELECT Id, StageName, AccountId, Admission_Category__c,
       Account.FullNameText__c, Position__r.Position__c, Position__r.Organization__c,
       Event__c, Event__r.City__r.Timezone__c
FROM Opportunity
WHERE StageName IN ('Invitation Sent','Registration In Progress','Closed/Registered')
  AND Event__r.Forum_Official_Programme__r.Start_Date__c > 2025-01-01
  AND AccountId IN ('0010X00004o0sOBQAY')
  AND Event__r.TopLink_Status_Final_Text__c NOT IN ('None')
  AND Event__r.Status__c IN ('Open','Activated')
  AND Event__r.City__r.Name NOT IN ('Unknown')
  AND Event__r.Country__r.Name NOT IN ('Unknown')
  AND Event__r.City__r.Timezone__c NOT IN ('')
  AND Event__r.Forum_Official_Programme__r.End_Date__c >= 2026-05-06;

EXPLAIN ANALYZE: not applicable; Salesforce SOQL.

22. Self registrations for event listing (SOQL)

Why: Adds events where the current account has an active registration.

Code: lib/eventws/repositories/events.rb:339-346; SOQL source lib/eventws/datasources/salesforce/events.rb:792-848.

SELECT Id, Status__c, Participant__r.Id, Participant__r.FullNameText__c,
       Position__r.Position__c, Position__r.Organization__c,
       Event__r.Id, Opportunity__c
FROM Registration__c
WHERE Status__c = 'Registered'
  AND Event__r.Forum_Official_Programme__r.Start_Date__c > 2025-01-01
  AND Participant__c IN ('0010X00004o0sOBQAY')
  AND Event__r.TopLink_Status_Final_Text__c NOT IN ('None')
  AND Event__r.Status__c IN ('Open','Activated')
  AND Event__r.City__r.Name NOT IN ('Unknown')
  AND Event__r.Country__r.Name NOT IN ('Unknown')
  AND Event__r.City__r.Timezone__c NOT IN ('')
  AND Event__r.Forum_Official_Programme__r.End_Date__c >= 2026-05-06;

EXPLAIN ANALYZE: not applicable; Salesforce SOQL.

23. Forum-staff delegate account/opportunity ids (SOQL)

Why: Finds delegated accounts and opportunity ids visible through PEM relationships.

Code: lib/eventws/repositories/events.rb:174-175; SOQL source lib/eventws/datasources/salesforce/login_delegators.rb:282-290.

SELECT Id, AccountId FROM Opportunity
WHERE Registration__r.RecordType.DeveloperName NOT IN ('Support')
  AND ((StageName IN ('Invitation Sent','Registration In Progress'))
       OR (Registration__r.Status__c IN ('Registered','Attended')))
  AND ( Account.Owner.Id = '0050X000009mt8mQAA'
     OR Participant_PEM__c = '0050X000009mt8mQAA'
     OR Registration__r.EventPEMUser__c = '0050X000009mt8mQAA'
     OR Registration__r.Parent_Registration__r.Participant__r.owner.Id = '0050X000009mt8mQAA'
     OR Registration__r.Parent_Registration__r.EventPEMUser__c = '0050X000009mt8mQAA'
     OR Account.Secondary_Engagement_Manager__r.Id = '0050X000009mt8mQAA'
     OR (Registration__r.RecordType.DeveloperName = 'Accompanying'
         AND Registration__r.Parent_Registration__r.Participant__c = '0010X00004o0sOBQAY'))
  AND AccountId != '0010X00004o0sOBQAY'
  AND Account.Toplink_Account_Status_Formula__c IN ('Active','Provisioned')
  AND Account.RecordType.DeveloperName IN ('Forum_Staff_Member','Constituent','Other')
  AND Event__r.Forum_Official_Programme__r.Start_Date__c >= 2025-01-01
ORDER BY Event__r.Start_Date__c DESC;

EXPLAIN ANALYZE: not applicable; Salesforce SOQL.

24-26. Delegate opportunities, batched (SOQL)

Why: Loads delegate opportunities for event listing. The trace produced three batches because the delegate-account list exceeded the 400-id batch size.

Code: lib/eventws/datasources/salesforce/events.rb:207-208; batching helper lib/eventws/datasources/salesforce/utils/sfid_by_batch.rb:37-57.

SELECT Id, StageName, AccountId, Admission_Category__c, ...
FROM Opportunity
WHERE StageName IN ('Invitation Sent','Registration In Progress','Closed/Registered')
  AND AccountId IN (:delegate_account_id_batch)
  AND Event__r.TopLink_Status_Final_Text__c NOT IN ('None')
  AND Event__r.Status__c IN ('Open','Activated')
  AND Event__r.Forum_Official_Programme__r.End_Date__c >= 2026-05-06;

EXPLAIN ANALYZE: not applicable; Salesforce SOQL.

27-29. Delegate registrations, batched (SOQL)

Why: Loads delegate registrations for event listing. Same three-batch shape as delegate opportunities.

Code: lib/eventws/datasources/salesforce/events.rb:220-221.

SELECT Id, Status__c, Participant__r.Id, Participant__r.FullNameText__c, ...
FROM Registration__c
WHERE Status__c = 'Registered'
  AND Participant__c IN (:delegate_account_id_batch)
  AND Event__r.TopLink_Status_Final_Text__c NOT IN ('None')
  AND Event__r.Status__c IN ('Open','Activated')
  AND Event__r.Forum_Official_Programme__r.End_Date__c >= 2026-05-06;

EXPLAIN ANALYZE: not applicable; Salesforce SOQL.

30. Unlisted private event ids

Why: Scope all adds unlisted private events after previous January.

Code: lib/eventws/repositories/events.rb:191-199.

SELECT event__c.sfid
FROM event__c
INNER JOIN programme__c ON programme__c.sfid = event__c.forum_official_programme__c
WHERE event__c.toplink_status_final_text__c IN ('Unlisted')
  AND programme__c.start_date__c > '2025-01-01';

EXPLAIN ANALYZE: 3.523 ms. No immediate concern.

31-33. Final event data fetch, batched (SOQL)

Why: Fetches full event payload for candidate event ids. This is the data that becomes event entries in the response.

Code: lib/eventws/datasources/salesforce/events.rb:184-191; SOQL source lib/eventws/datasources/salesforce/events.rb:631-648.

SELECT Id, Name, Slug__c, Toplink_Status_Final_Text__c, Status__c, ...
FROM Event__c
WHERE Toplink_Status_Final_Text__c NOT IN ('None')
  AND Status__c IN ('Open','Activated')
  AND City__r.Name NOT IN ('Unknown')
  AND Country__r.Name NOT IN ('Unknown')
  AND City__r.Timezone__c NOT IN ('')
  AND Forum_Official_Programme__r.End_Date__c >= 2026-05-06
  AND Id IN (:event_id_batch)
ORDER BY Forum_Official_Programme__r.End_Date__c ASC, Id ASC
LIMIT 100;

EXPLAIN ANALYZE: not applicable; Salesforce SOQL. The local equivalent would be an event__c/programme__c/city__c/country__c join filtered by sfid IN (:event_id_batch).

Event branch, iteration 2 (`page_index=2`)

The mixed-listing event loop repeats the event branch because iteration 1 returned 13 events and the loop target for per_page=100 is 200 events. Iteration 2 uses the same filters and asks the event data SOQL for the second page (OFFSET 100). It returned 0 events and stopped the loop.

34. Public publishable event ids repeated

Why: Rebuilds candidate event ids for page 2.

Code/SQL: same as query 17.

EXPLAIN ANALYZE: same shape as query 17; measured at 2.440 ms.

35. Open-registration event ids repeated

Why: Rebuilds open-registration candidates for page 2.

Code/SQL: same as query 18.

EXPLAIN ANALYZE: same shape as query 18; measured at 1.254 ms.

36. Operational-contact position lookup repeated

Why: Event listing recomputes OC position again for page 2.

Code/SQL: same as query 1.

EXPLAIN ANALYZE: same shape as query 1; measured at 0.156 ms.

37-46. Event-team/self/delegate SOQL repeated

Why: The page-2 event listing cache key differs by page, so the cold trace repeats event-team, self opportunity, self registration, delegate account/opportunity, delegate opportunity batches, and delegate registration batches.

Code/SQL: same as queries 20-29.

EXPLAIN ANALYZE: not applicable; Salesforce SOQL.

47. Unlisted private event ids repeated

Why: Scope all repeats unlisted private event ids for page 2.

Code/SQL: same as query 30.

EXPLAIN ANALYZE: same shape as query 30; measured at 3.523 ms.

48-50. Final event data fetch, page 2 batches (SOQL)

Why: Fetches the second 100-event slice for the same candidate ids.

Code: lib/eventws/datasources/salesforce/events.rb:184-191.

SELECT Id, Name, Slug__c, Toplink_Status_Final_Text__c, Status__c, ...
FROM Event__c
WHERE Toplink_Status_Final_Text__c NOT IN ('None')
  AND Status__c IN ('Open','Activated')
  AND City__r.Name NOT IN ('Unknown')
  AND Country__r.Name NOT IN ('Unknown')
  AND City__r.Timezone__c NOT IN ('')
  AND Forum_Official_Programme__r.End_Date__c >= 2026-05-06
  AND Id IN (:event_id_batch)
ORDER BY Forum_Official_Programme__r.End_Date__c ASC, Id ASC
LIMIT 100 OFFSET 100;

EXPLAIN ANALYZE: not applicable; Salesforce SOQL.

Rendering-time SQL

The previous endpoint trace observed rendering-time SQL when MixedListingSessions asks SessionService#display_session_structures?.

51. Account load for rendering-time delegator check

Why: SessionService#find_delegators_ids loads the current account.

Code: lib/eventws/presenters/mixed_listing_sessions.rb:6-17; lib/eventws/services/session/session_service.rb:202-214,306-314.

SELECT account.*
FROM account
WHERE account.toplink_account_status_text__c IN ('Active','Provisioned')
  AND account.ispersonaccount = TRUE
  AND account.sfid = '0010X00004o0sOBQAY'
LIMIT 1;

EXPLAIN ANALYZE: same access shape as query 2; expected sub-millisecond via hcu_idx_account_sfid.

52. Active group/local/personal operational contacts

Why: Rendering-time delegator checks load active OC rows.

SELECT operational_contact__c.*
FROM operational_contact__c
WHERE operational_contact__c.status__c = 'Active'
  AND operational_contact__c.operational_contact__c = '0010X00004o0sOBQAY'
  AND operational_contact__c.type__c IN ('Group','Local','Personal');

EXPLAIN ANALYZE: expected to use index_operational_contact_on_contact_and_status; query 1 already showed the same contact/status index returning in 0.018 ms inside the join.

53. Active event operational contacts

Why: Rendering-time delegator checks load active event OC rows separately.

SELECT operational_contact__c.*
FROM operational_contact__c
WHERE operational_contact__c.status__c = 'Active'
  AND operational_contact__c.operational_contact__c = '0010X00004o0sOBQAY'
  AND operational_contact__c.type__c = 'Event';

EXPLAIN ANALYZE: expected to use index_operational_contact_on_contact_and_status; no concern.

Potentially poor queries and index candidates

These are candidates, not migrations applied in this research. They should be verified with production-like data and EXPLAIN ANALYZE before adding.

A. Query 10 and 14: role lookups by both account and session

Observed: Query 10 probes roles by session__c and then filters by constituent__c; query 14 bitmap-ANDs separate session__c and constituent__c indexes. Query 10 took 43.563 ms; query 14 took 7.114 ms.

Candidate index:

add_index :role__c, [:session__c, :constituent__c], algorithm: :concurrently

If query 14 is the main target, test the reverse order too:

add_index :role__c, [:constituent__c, :session__c], algorithm: :concurrently

Do not add both without measuring. For this endpoint, [:session__c, :constituent__c] is more likely to help query 10 because the current plan starts from sessions and probes roles by session.

B. Query 12: session-team-member lookup by session, status, and forum staff member

Observed: Query 12 probes session_team_member__c by session__c, then filters by status__c and forum_staff_member__c. It took 29.303 ms and returned 0 rows.

Candidate index:

add_index :session_team_member__c, [:session__c, :status__c, :forum_staff_member__c], algorithm: :concurrently

Also test this order if most requests have a small forum-staff/delegate set:

add_index :session_team_member__c, [:forum_staff_member__c, :status__c, :session__c], algorithm: :concurrently

C. Query 10/12: future session-logistics filter

Observed: Both queries start from open community sessions, probe logistics by session__c, then filter on TECH_EndDateTimeUTC__c and not-null date/time fields. This removed most candidate sessions before role/team-member lookup.

Candidate index:

add_index :session_logistics__c,
  [:session__c, :tech_enddatetimeutc__c],
  where: "sessionenddate__c IS NOT NULL AND sessionstartdate__c IS NOT NULL AND sessionendtime__c IS NOT NULL AND sessionstarttime__c IS NOT NULL",
  algorithm: :concurrently

This may help when the planner keeps the current join order. If a query can be rewritten to start from future logistics, test the reverse order instead:

add_index :session_logistics__c,
  [:tech_enddatetimeutc__c, :session__c],
  where: "sessionenddate__c IS NOT NULL AND sessionstartdate__c IS NOT NULL AND sessionendtime__c IS NOT NULL AND sessionstarttime__c IS NOT NULL",
  algorithm: :concurrently

D. Query 4 and 5: PEM delegate expansion

Observed: The two slowest SQL queries are the PEM delegate expansions (135.267 ms and 134.484 ms). The plans already use many existing indexes. The cost is mostly caused by broad domain logic: seven UNION arms and large role/session joins.

Index recommendation: no single obvious index from this run. Query 5 may benefit indirectly from the role__c(session__c, constituent__c) candidate because it selects role constituents after joining by session, but this should be tested; the local plan generated ~243,950 role rows.

Reproduction notes

The local EXPLAIN ANALYZE script used Rails runner, generated the same account/session id sets first, then ran EXPLAIN (ANALYZE, BUFFERS, VERBOSE) for each SQL statement. The script is not part of the gist, but the important measurement outputs are included above.

Caveats

SOQL calls cannot be analyzed by local Postgres. Their performance depends on Salesforce query planning and network latency.
The event branch runs through Salesforce REST in the app, even though some candidate-id queries use local Postgres.
Large IN (...) lists are abbreviated in this document for readability.
Measurements are from the local development database and can differ from production data distribution.

andreimaxim/events_sessions_trace_with_explain.md

Endpoint trace with SQL and EXPLAIN ANALYZE

Summary

Entrypoint and control flow

Route/controller

Filter construction

Mixed listing dispatch

Query sequence

1. Operational-contact position for filter construction

2. Forum-staff record load

3. Published events since previous January

4. Current PEM delegates CTE

5. Session PEM accounts

6. Final managed accounts

7. User community memberships

8. Active communities by membership ids

9. Forum-staff team-member communities

10. Community sessions where self/managed accounts have roles

11. Community/project sessions open to matching communities

12. Community sessions where account/delegates are session team members

13. Open TopLink community sessions

14. Roles for resolved account ids and session ids

15. Session payload for resolved session ids

16. Session organisers for resolved sessions

Event branch, iteration 1 (page_index=1)

17. Public publishable event ids

18. Open-registration event ids

19. Operational-contact position lookup repeated for event listing

20. Event-team-member event ids (SOQL)

21. Self opportunities for event listing (SOQL)

22. Self registrations for event listing (SOQL)

23. Forum-staff delegate account/opportunity ids (SOQL)

24-26. Delegate opportunities, batched (SOQL)

27-29. Delegate registrations, batched (SOQL)

30. Unlisted private event ids

31-33. Final event data fetch, batched (SOQL)

Event branch, iteration 2 (page_index=2)

34. Public publishable event ids repeated

35. Open-registration event ids repeated

36. Operational-contact position lookup repeated

37-46. Event-team/self/delegate SOQL repeated

47. Unlisted private event ids repeated

48-50. Final event data fetch, page 2 batches (SOQL)

Rendering-time SQL

51. Account load for rendering-time delegator check

52. Active group/local/personal operational contacts

53. Active event operational contacts

Potentially poor queries and index candidates

A. Query 10 and 14: role lookups by both account and session

B. Query 12: session-team-member lookup by session, status, and forum staff member

C. Query 10/12: future session-logistics filter

D. Query 4 and 5: PEM delegate expansion

Reproduction notes

Caveats

Event branch, iteration 1 (`page_index=1`)

Event branch, iteration 2 (`page_index=2`)