ehddnr301 commented on PR #74552: URL: https://github.com/apache/airflow/pull/74552#issuecomment-6170816122
@ashb, I moved the fix into the common prefix-search code to find the missing non-ASCII matches. I thought a minimal change would leave fewer things to consider when revisiting the implementation later. **The missing `카페` and `café` matches are returned.** The added condition skips the upper bound when its incremented character is non-ASCII. The lower bound and substring equality stay; ASCII searches are unchanged. **Search results and timing** These are `카페` results on a PostgreSQL 16 en_US.utf8 indexed column. Times are count + list totals (ms, warm cache). | Table rows | Matches before | Matches now | Before (ms) | Now (ms) | |---:|---:|---:|---:|---:| | 1,000 | 0 | 2 | 0.015 | 0.352 | | 10,000 | 0 | 2 | 0.015 | 2.977 | | 100,000 | 0 | 20 | 0.017 | 34.354 | | 200,000 | 0 | 40 | 0.015 | 69.550 | | 1,000,000 | 0 | 200 | 0.019 | 252.753 | **The old zero counts are missed matches.** They do not represent a faster query returning the same results. These timings exclude HTTP overhead; regressions for other terms and collations are included below. **I would fix the search failures here and handle further performance work in follow-up PRs.** The `카페` search above took about 0.25 seconds even at 1m rows. I judged that this query time would not significantly hurt the search experience. Could you review whether this is suitable to merge with that scope? <details> <summary>Experiment setup and tables (2–49-character terms)</summary> **Benchmark** These tables show four 2–49-character terms from the PostgreSQL 16 measurements at 1k, 10k, 100k, 200k and 1m rows. They cover two collations and both an indexed column and a CASE expression like Dag display names. 90% of the synthetic values contain non-ASCII characters (e.g. `커피매출000000001`, `카asdfasdfasdf페000000007`). These are times after the change. The first two searches use the en_US.utf8 indexed column; the last column is the slowest result for the four terms shown, across both collations and query shapes. | Table rows | 카페 search (ms) | 16-char no-match (ms) | Maximum for the four terms below (ms) | |---:|---:|---:|---:| | 1,000 | 0.352 | 0.063 | 2.808 | | 10,000 | 2.977 | 0.407 | 26.485 | | 100,000 | 34.354 | 8.069 | 271.374 | | 200,000 | 69.550 | 17.001 | 541.970 | | 1,000,000 | 252.753 | 179.740 | 2019.372 | Warm cache, one warmup and three measured runs per query. Times sum the medians of count and `ORDER BY id LIMIT 100`, excluding HTTP overhead. All before/after results are below. **The four search terms shown** - 카페 (2 chars): `카페` - Mixed prefix (14 chars): `카asdfasdfasdf페` - 49-char sentence (49 chars): `카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페` - Mixed, no match (16 chars): `카asdfasdfasdf없는페` **en_US.utf8 / indexed column: 20 comparisons** | Table rows | Search | Chars | Matches before | Matches now | Before (ms) | Now (ms) | |---:|---|---:|---:|---:|---:|---:| | 1,000 | 카페 | 2 | 0 (miss) | 2 | 0.015 | 0.352 | | 1,000 | Mixed prefix | 14 | 0 (miss) | 1 | 0.015 | 0.058 | | 1,000 | 49-char sentence | 49 | 0 (miss) | 2 | 0.045 | 2.808 | | 1,000 | Mixed, no match | 16 | 0 | 0 | 0.015 | 0.063 | | 10,000 | 카페 | 2 | 0 (miss) | 2 | 0.015 | 2.977 | | 10,000 | Mixed prefix | 14 | 0 (miss) | 1 | 0.018 | 0.397 | | 10,000 | 49-char sentence | 49 | 0 (miss) | 2 | 0.054 | 26.308 | | 10,000 | Mixed, no match | 16 | 0 | 0 | 0.018 | 0.407 | | 100,000 | 카페 | 2 | 0 (miss) | 20 | 0.017 | 34.354 | | 100,000 | Mixed prefix | 14 | 0 (miss) | 10 | 0.019 | 7.970 | | 100,000 | 49-char sentence | 49 | 0 (miss) | 20 | 0.063 | 270.176 | | 100,000 | Mixed, no match | 16 | 0 | 0 | 0.017 | 8.069 | | 200,000 | 카페 | 2 | 0 (miss) | 40 | 0.015 | 69.550 | | 200,000 | Mixed prefix | 14 | 0 (miss) | 20 | 0.017 | 16.697 | | 200,000 | 49-char sentence | 49 | 0 (miss) | 40 | 0.068 | 536.694 | | 200,000 | Mixed, no match | 16 | 0 | 0 | 0.017 | 17.001 | | 1,000,000 | 카페 | 2 | 0 (miss) | 200 | 0.019 | 252.753 | | 1,000,000 | Mixed prefix | 14 | 0 (miss) | 100 | 0.020 | 177.925 | | 1,000,000 | 49-char sentence | 49 | 0 (miss) | 200 | 0.071 | 2002.212 | | 1,000,000 | Mixed, no match | 16 | 0 | 0 | 0.018 | 179.740 | **en_US.utf8 / display-name CASE: 20 comparisons** | Table rows | Search | Chars | Matches before | Matches now | Before (ms) | Now (ms) | |---:|---|---:|---:|---:|---:|---:| | 1,000 | 카페 | 2 | 0 (miss) | 2 | 0.404 | 0.338 | | 1,000 | Mixed prefix | 14 | 0 (miss) | 1 | 0.242 | 0.240 | | 1,000 | 49-char sentence | 49 | 0 (miss) | 2 | 4.925 | 2.689 | | 1,000 | Mixed, no match | 16 | 0 | 0 | 0.227 | 0.235 | | 10,000 | 카페 | 2 | 0 (miss) | 2 | 3.710 | 3.124 | | 10,000 | Mixed prefix | 14 | 0 (miss) | 1 | 2.049 | 2.153 | | 10,000 | 49-char sentence | 49 | 0 (miss) | 2 | 48.831 | 26.485 | | 10,000 | Mixed, no match | 16 | 0 | 0 | 2.054 | 2.152 | | 100,000 | 카페 | 2 | 0 (miss) | 20 | 39.987 | 37.256 | | 100,000 | Mixed prefix | 14 | 0 (miss) | 10 | 23.309 | 26.732 | | 100,000 | 49-char sentence | 49 | 0 (miss) | 20 | 492.300 | 271.374 | | 100,000 | Mixed, no match | 16 | 0 | 0 | 23.218 | 26.669 | | 200,000 | 카페 | 2 | 0 (miss) | 40 | 79.546 | 73.783 | | 200,000 | Mixed prefix | 14 | 0 (miss) | 20 | 47.540 | 53.425 | | 200,000 | 49-char sentence | 49 | 0 (miss) | 40 | 982.313 | 541.970 | | 200,000 | Mixed, no match | 16 | 0 | 0 | 47.040 | 52.974 | | 1,000,000 | 카페 | 2 | 0 (miss) | 200 | 398.353 | 266.703 | | 1,000,000 | Mixed prefix | 14 | 0 (miss) | 100 | 235.440 | 268.431 | | 1,000,000 | 49-char sentence | 49 | 0 (miss) | 200 | 4951.882 | 2019.372 | | 1,000,000 | Mixed, no match | 16 | 0 | 0 | 235.867 | 264.860 | **C / indexed column: 20 comparisons** | Table rows | Search | Chars | Matches before | Matches now | Before (ms) | Now (ms) | |---:|---|---:|---:|---:|---:|---:| | 1,000 | 카페 | 2 | 2 | 2 | 0.016 | 0.151 | | 1,000 | Mixed prefix | 14 | 1 | 1 | 0.015 | 0.225 | | 1,000 | 49-char sentence | 49 | 2 | 2 | 0.017 | 0.225 | | 1,000 | Mixed, no match | 16 | 0 | 0 | 0.013 | 0.205 | | 10,000 | 카페 | 2 | 2 | 2 | 0.019 | 1.236 | | 10,000 | Mixed prefix | 14 | 1 | 1 | 0.019 | 1.919 | | 10,000 | 49-char sentence | 49 | 2 | 2 | 0.021 | 1.927 | | 10,000 | Mixed, no match | 16 | 0 | 0 | 0.013 | 1.923 | | 100,000 | 카페 | 2 | 20 | 20 | 0.030 | 16.987 | | 100,000 | Mixed prefix | 14 | 10 | 10 | 0.026 | 23.745 | | 100,000 | 49-char sentence | 49 | 20 | 20 | 0.037 | 23.747 | | 100,000 | Mixed, no match | 16 | 0 | 0 | 0.016 | 23.687 | | 200,000 | 카페 | 2 | 40 | 40 | 0.048 | 34.964 | | 200,000 | Mixed prefix | 14 | 20 | 20 | 0.028 | 48.684 | | 200,000 | 49-char sentence | 49 | 40 | 40 | 0.053 | 48.811 | | 200,000 | Mixed, no match | 16 | 0 | 0 | 0.017 | 48.153 | | 1,000,000 | 카페 | 2 | 200 | 200 | 0.122 | 122.964 | | 1,000,000 | Mixed prefix | 14 | 100 | 100 | 0.076 | 237.350 | | 1,000,000 | 49-char sentence | 49 | 200 | 200 | 0.196 | 174.874 | | 1,000,000 | Mixed, no match | 16 | 0 | 0 | 0.013 | 240.279 | **C / display-name CASE: 20 comparisons** | Table rows | Search | Chars | Matches before | Matches now | Before (ms) | Now (ms) | |---:|---|---:|---:|---:|---:|---:| | 1,000 | 카페 | 2 | 2 | 2 | 0.141 | 0.190 | | 1,000 | Mixed prefix | 14 | 1 | 1 | 0.113 | 0.219 | | 1,000 | 49-char sentence | 49 | 2 | 2 | 0.117 | 0.222 | | 1,000 | Mixed, no match | 16 | 0 | 0 | 0.110 | 0.220 | | 10,000 | 카페 | 2 | 2 | 2 | 1.022 | 1.459 | | 10,000 | Mixed prefix | 14 | 1 | 1 | 1.021 | 2.122 | | 10,000 | 49-char sentence | 49 | 2 | 2 | 1.041 | 2.121 | | 10,000 | Mixed, no match | 16 | 0 | 0 | 0.994 | 2.124 | | 100,000 | 카페 | 2 | 20 | 20 | 12.637 | 19.008 | | 100,000 | Mixed prefix | 14 | 10 | 10 | 12.683 | 26.297 | | 100,000 | 49-char sentence | 49 | 20 | 20 | 12.721 | 26.202 | | 100,000 | Mixed, no match | 16 | 0 | 0 | 12.197 | 25.877 | | 200,000 | 카페 | 2 | 40 | 40 | 26.239 | 39.269 | | 200,000 | Mixed prefix | 14 | 20 | 20 | 25.914 | 53.219 | | 200,000 | 49-char sentence | 49 | 40 | 40 | 26.452 | 52.663 | | 200,000 | Mixed, no match | 16 | 0 | 0 | 25.760 | 53.319 | | 1,000,000 | 카페 | 2 | 200 | 200 | 132.432 | 141.651 | | 1,000,000 | Mixed prefix | 14 | 100 | 100 | 133.317 | 261.058 | | 1,000,000 | 49-char sentence | 49 | 200 | 200 | 135.488 | 193.879 | | 1,000,000 | Mixed, no match | 16 | 0 | 0 | 129.474 | 264.647 | Match counts are totals before LIMIT. “Miss” marks an incorrect result; those faster queries are not equivalent-result performance improvements. **Earlier 200k-row benchmark: all before/after results across three databases** Different dataset, warm cache, sum of medians from 21 runs of count and ORDER BY name LIMIT 50. This is separate from the size matrix. Match counts follow the earlier correctness checks and the preserved data generator. | Table rows | Non-ASCII | Term | DB | Matches before | Matches now | Before (ms) | Now (ms) | |---:|---|---|---|---:|---:|---:|---:| | 200,000 | 50% | `dag_0012` | PostgreSQL 16 | 500 | 500 | 0.370 | 0.330 | | 200,000 | 50% | `dag_0012` | MySQL 8.0 | 500 | 500 | 0.340 | 0.350 | | 200,000 | 50% | `dag_0012` | SQLite | 500 | 500 | 0.070 | 0.070 | | 200,000 | 50% | `münchen` | PostgreSQL 16 | 16,666 | 16,666 | 4.690 | 4.690 | | 200,000 | 50% | `카페a` | PostgreSQL 16 | 0 | 0 | 0.190 | 0.170 | | 200,000 | 50% | `카페` | PostgreSQL 16 | 0 (miss) | 16,667 | 0.160 | 15.890 | | 200,000 | 50% | `café` | PostgreSQL 16 | 0 (miss) | 16,667 | 0.160 | 14.630 | | 200,000 | 50% | `카페` | MySQL 8.0 | 16,667 | 16,667 | 3.360 | 4.220 | | 200,000 | 50% | `café` | MySQL 8.0 | 0 (miss) | 16,667 | 0.130 | 21.560 | | 200,000 | 50% | `카페` | SQLite | 16,667 | 16,667 | 1.000 | 1.510 | | 200,000 | 50% | `café` | SQLite | 16,667 | 16,667 | 1.010 | 9.350 | | 200,000 | 2% | `dag_0012` | PostgreSQL 16 | 980 | 980 | 0.500 | 0.470 | | 200,000 | 2% | `카페` | PostgreSQL 16 | 0 (miss) | 667 | 0.120 | 12.060 | | 200,000 | 2% | `café` | PostgreSQL 16 | 0 (miss) | 667 | 0.120 | 11.130 | </details> --- Drafted-by: Codex (GPT-6); reviewed by ehddnr301 before posting -- 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]
