sameerpandey17 commented on PR #75635: URL: https://github.com/apache/airflow/pull/75635#issuecomment-6655702752
@SameerMesiah97 thanks for the review, and good question. The lower bound is not safe, so I've pushed a follow-up that removes it. The filter is now only `substr(col, 1, len) = prefix`. What I found: - ICU: across 853 locales and 4,421 locale/collation combinations, `col >= prefix` misses valid matches for ASCII prefixes in Welsh (`n` misses `ng...`) and Igbo (`c` misses `ch...`), plus some non-ASCII scripts. I also confirmed it on PostgreSQL 16. - Benchmark on PostgreSQL 16 at 1M and 10M rows with `cs-CZ`, `cy` and `ig` ICU collations, checking every result against `starts_with()`: the lower-bound-only version still returns wrong rows for Welsh `n` (0 of 10,000) and Igbo `c` (10,000 missing). The exact-prefix version is correct in all 36 combinations. Cost: it is a full scan, so about 30 ms per query at 1M rows and 260-420 ms at 10M. A selective prefix at 10M rows goes from ~2.6 ms (original) and ~60 ms (lower bound only) to ~395 ms. Broad prefixes are only ~45-90% slower. Limits: synthetic data, one machine, `count(*)` only, not Airflow's real queries, and PostgreSQL only. The full numbers and method are in the updated description. If the maintainers would rather keep the index, I can try a prefix-friendly index (`text_pattern_ops` or a `"C"`-collated index) in a follow-up. I haven't tested that yet. Does this trade-off look acceptable to you? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
