sameerpandey17 commented on PR #75635: URL: https://github.com/apache/airflow/pull/75635#issuecomment-6704768127
Status update on this PR, and a question on direction before I do more work on it. Current state: both range bounds are removed, so prefix search is now only `substr(col, 1, len) = prefix`. I checked it on PostgreSQL 16 with `cs-CZ`, `cy` and `ig` ICU collations against `starts_with()` (1M and 10M rows). This version returned the right rows in all 36 collation/prefix combinations I tried. The original (both bounds) and the lower-bound-only version each return wrong rows for some of them (Czech `c`, Welsh `n`, Igbo `c`). `test_parameters.py`: 71 passed, 0 skipped on Postgres 16 in Breeze. Cost: it is a full scan, about 30 ms per query at 1M rows and 260-420 ms at 10M. Selective prefixes were ~0.2-8 ms before. Not tested: Airflow's real queries, other databases. Not done yet, waiting on direction: 1. The public API descriptions (and the generated OpenAPI/TypeScript files) still call prefix patterns "index-friendly", which is no longer true. 2. A docstring tidy. 3. A small test cleanup (dropping an assertion that depends on how one ICU version orders letters). I'd rather not regenerate the generated files until the approach is agreed. Question: is this trade-off acceptable? If yes, I'll do the cleanup above in this PR. If not, the alternatives I can see are (a) keep only the lower bound and document that it still misses matches for Welsh, Igbo and some non-ASCII scripts, (b) try a prefix-friendly index (e.g. `text_pattern_ops`) to win the speed back (not tested), or (c) another approach you'd prefer. -- 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]
