ehddnr301 commented on PR #74552:
URL: https://github.com/apache/airflow/pull/74552#issuecomment-6193615870
@ashb, here are the EXPLAIN ANALYZE outputs for the 1m-row cases!
### Prefix search on 1m rows
With the same setup, the 1m-row `카페` search reran at **252.186 ms** for
count + page (previously 252.753 ms).
Count uses a full table scan. The page query uses the primary-key index for
`ORDER BY id`, filtering until it finds 100 matches. Neither uses the name
index to narrow the prefix search.
| Term | Earlier total (ms) | Rerun total (ms) |
|---|---:|---:|
| `카페` | 252.753 | 252.186 |
| 49-character term | 2002.212 | 1994.487 |
<details>
<summary>1m rows: the ~250 ms 카페 case — SQL / EXPLAIN ANALYZE</summary>
Setup: PostgreSQL 16.15 / `en_US.utf8`, temporary tables populated with
generated test data, warm cache, `ORDER BY id LIMIT 100`. Totals sum the count
and page execution-time medians from three runs after one warmup, excluding
planning time. Text plans are additional executions and can differ slightly
from the tabulated medians.
**Total-count query**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) AS count_1
FROM (
SELECT long_prefix_probe.id AS id
FROM long_prefix_probe
WHERE
long_prefix_probe.name_locale >= '카페'
AND substr(long_prefix_probe.name_locale, 1, 2) = '카페'
) AS anon_1;
```
```text
Aggregate (cost=29722.50..29722.51 rows=1 width=8) (actual
time=163.969..163.970 rows=1 loops=1)
Buffers: local hit=1 read=12209
-> Seq Scan on long_prefix_probe (cost=0.00..29710.00 rows=5000 width=0)
(actual time=0.006..163.953 rows=200 loops=1)
Filter: ((name_locale >= '카페'::text) AND (substr(name_locale, 1, 2)
= '카페'::text))
Rows Removed by Filter: 999800
Buffers: local hit=1 read=12209
Planning:
Buffers: local read=4
Planning Time: 0.073 ms
Execution Time: 163.984 ms
```
**Page query**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT long_prefix_probe.id
FROM long_prefix_probe
WHERE
long_prefix_probe.name_locale >= '카페'
AND substr(long_prefix_probe.name_locale, 1, 2) = '카페'
ORDER BY long_prefix_probe.id
LIMIT 100;
```
```text
Limit (cost=0.42..914.28 rows=100 width=4) (actual time=0.011..89.205
rows=100 loops=1)
Buffers: local hit=6 read=7324
-> Index Scan using long_prefix_probe_pkey on long_prefix_probe
(cost=0.42..45693.43 rows=5000 width=4) (actual time=0.010..89.198 rows=100
loops=1)
Filter: ((name_locale >= '카페'::text) AND (substr(name_locale, 1, 2)
= '카페'::text))
Rows Removed by Filter: 489909
Buffers: local hit=6 read=7324
Planning:
Buffers: local read=4
Planning Time: 0.058 ms
Execution Time: 89.214 ms
```
</details>
### The 49-character term
The 49-character search uses the same scan types and spends more time in the
scan nodes.
<details>
<summary>1m rows: 49-character term — SQL / EXPLAIN ANALYZE</summary>
**Total-count query**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) AS count_1
FROM (
SELECT long_prefix_probe.id AS id
FROM long_prefix_probe
WHERE
long_prefix_probe.name_locale >=
'카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'
AND substr(long_prefix_probe.name_locale, 1, 49) =
'카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'
) AS anon_1;
```
```text
Aggregate (cost=29722.50..29722.51 rows=1 width=8) (actual
time=1331.468..1331.469 rows=1 loops=1)
Buffers: local hit=1 read=12209
-> Seq Scan on long_prefix_probe (cost=0.00..29710.00 rows=5000 width=0)
(actual time=0.016..1331.449 rows=200 loops=1)
Filter: ((name_locale >= '카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를
보고하는 카페'::text) AND (substr(name_locale, 1, 49) = '카페에서 매일 수집한 매출 데이터를 지역과 상품별로
정리하고 검증한 결과를 보고하는 카페'::text))
Rows Removed by Filter: 999800
Buffers: local hit=1 read=12209
Planning:
Buffers: local read=4
Planning Time: 0.085 ms
Execution Time: 1331.483 ms
```
**Page query**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT long_prefix_probe.id
FROM long_prefix_probe
WHERE
long_prefix_probe.name_locale >=
'카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'
AND substr(long_prefix_probe.name_locale, 1, 49) =
'카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를 보고하는 카페'
ORDER BY long_prefix_probe.id
LIMIT 100;
```
```text
Limit (cost=0.42..914.28 rows=100 width=4) (actual time=0.021..663.904
rows=100 loops=1)
Buffers: local hit=6 read=7324
-> Index Scan using long_prefix_probe_pkey on long_prefix_probe
(cost=0.42..45693.43 rows=5000 width=4) (actual time=0.021..663.896 rows=100
loops=1)
Filter: ((name_locale >= '카페에서 매일 수집한 매출 데이터를 지역과 상품별로 정리하고 검증한 결과를
보고하는 카페'::text) AND (substr(name_locale, 1, 49) = '카페에서 매일 수집한 매출 데이터를 지역과 상품별로
정리하고 검증한 결과를 보고하는 카페'::text))
Rows Removed by Filter: 489909
Buffers: local hit=6 read=7324
Planning:
Buffers: local read=4
Planning Time: 0.088 ms
Execution Time: 663.917 ms
```
</details>
---
Drafted-by: Codex (GPT-6.1), Claude (5.5); 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]
