Are Your YSQL Sessions Connecting to the Preferred Region?

				
					SELECT now() - interval '10 minutes' AS cutoff \gset

WITH preferred_zones AS (
    SELECT DISTINCT
        preference.preference_rank::integer AS preference_rank,
        zone.value->>'placementCloud' AS cloud,
        zone.value->>'placementRegion' AS region,
        zone.value->>'placementZone' AS zone
    FROM yb_universe_config AS cfg
    CROSS JOIN LATERAL jsonb_array_elements(
        COALESCE(
            cfg.config #> '{replicationInfo,multiAffinitizedLeaders}',
            '[]'::jsonb
        )
    ) WITH ORDINALITY AS preference(value, preference_rank)
    CROSS JOIN LATERAL jsonb_array_elements(
        COALESCE(preference.value->'zones', '[]'::jsonb)
    ) AS zone(value)
),
preference_summary AS (
    SELECT count(*) AS configured_zone_count
    FROM preferred_zones
),
activity_samples AS (
    SELECT
        s.host AS executing_node,
        s.cloud AS executing_cloud,
        s.region AS executing_region,
        s.zone AS executing_zone,
        ash.top_level_node_id,
        ash.pid,
        ash.query_id,
        ash.client_node_ip,
        ash.sample_time,
        ash.wait_event,
        ash.wait_event_type,
        matched.preference_rank
    FROM gv$yb_active_session_history AS ash
    JOIN yb_servers() AS s
      ON ash.top_level_node_id = s.uuid::uuid
    LEFT JOIN LATERAL (
        SELECT min(p.preference_rank) AS preference_rank
        FROM preferred_zones AS p
        WHERE p.cloud = s.cloud
          AND (p.region = s.region OR p.region = '*')
          AND (p.zone = s.zone OR p.zone = '*')
    ) AS matched ON true
    WHERE ash.sample_time >= :'cutoff'
      AND ash.client_node_ip IS NOT NULL
      AND ash.wait_event_component = 'YSQL'
)
SELECT
    a.executing_node,
    a.executing_cloud,
    a.executing_region,
    a.executing_zone,
    a.top_level_node_id,
    a.pid,
    a.query_id,
    a.client_node_ip,
    a.preference_rank,
    CASE
        WHEN ps.configured_zone_count = 0
            THEN 'NO BASELINE: no cluster-level preference found'
        WHEN a.preference_rank = 1
            THEN 'FIRST CHOICE: rank 1'
        WHEN a.preference_rank > 1
            THEN 'REVIEW: fallback rank ' ||
                 a.preference_rank::text
        ELSE 'REVIEW: unranked location'
    END AS placement_check,
    min(a.sample_time) AS first_sample,
    max(a.sample_time) AS last_sample,
    count(*) AS captured_samples,
    a.wait_event,
    a.wait_event_type
FROM activity_samples AS a
CROSS JOIN preference_summary AS ps
GROUP BY
    a.executing_node,
    a.executing_cloud,
    a.executing_region,
    a.executing_zone,
    a.top_level_node_id,
    a.pid,
    a.query_id,
    a.client_node_ip,
    a.preference_rank,
    ps.configured_zone_count,
    a.wait_event,
    a.wait_event_type
ORDER BY
    a.executing_region,
    a.executing_node,
    captured_samples DESC;