- Shipped
- September 23, 2026 at 1:37 PM UTC
- Author
- Kamo
- Commit
- cf061b2
kbservice's public KB endpoints and its translation-retry sweep both asked KbArticleTranslationRepository one row at a time. Production's pg_stat_statements showed two very different costs from that: - findRealTranslationLocales (new): backs the sitemap/hreflang lookup that tells a real translation from TranslateService's English-fallback rows (a row can hold English under a locale key when no provider exists for that direction). This cluster plans with YugabyteDB's legacy heuristic model (no optimizer statistics) and chose a Nested Loop for the JPQL join of kb_article_translations to kb_articles: an index scan for the uid list, then a separate single-row index lookup into kb_articles for every one of those rows — 6,468 storage RPCs for a 308-article org, 11.9 s, to join two tables that together fit in memory (~6,000 calls at ~11 s each in production). Native SQL with a pg_hint_plan HashJoin hint pins the join instead (same technique as **************** Verified with EXPLAIN on production: 11.9 s -> 0.2 s, stable under plan_cache_mode = force_generic_plan (the shape JDBC's server-side prepares actually run under). - countGroupedByArticleUid (new): the ten-minute translation-retry sweep asked countByArticle_Uid once per published article to find which ones still need locales — 1.7M calls in production, each sub-millisecond alone. One grouped COUNT over the whole batch is the same answer; a uid absent from the result has zero rows. kbservice wires both of these in (separate commit, its own repo).