Re: Terribly slow query with very good plan?

> > PostgreSQL uses seq scan for O, F, N, T letters, but it uses index scan
> for A, I, C letters (with the "like" query).
>
> That's interesting.
>
> Does it help if you create an additional index on relpath with the
> text_pattern_ops modifier, e.g.
>
> CREATE INDEX ... USING btree (relpath text_pattern_ops);
>

It does not help. Details below. (PostgreSQL version 12.8)

CREATE index test ON media.oo_file (relpath COLLATE "C");
CREATE INDEX test2 ON media.oo_file USING btree (relpath text_pattern_ops);
CREATE INDEX test3 ON media.oo_file USING btree (relpath collate "C"
text_pattern_ops);
-- letter "A" ^@ operator -> slow seq scan
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active and fi.relpath ^@ 'A' limit 1;
QUERY PLAN
|
----------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.00..1904.09 rows=1 width=8) (actual
time=10779.585..10779.587 rows=0 loops=1) |
Output: id
|
Buffers: shared hit=9960 read=121303
|
-> Seq Scan on media.oo_file fi (cost=0.00..144710.65 rows=76 width=8)
(actual time=10779.582..10779.583 rows=0 loops=1)|
Output: id
|
Filter: (fi.is_active AND (fi.relpath ^@ 'A'::text))
|
Rows Removed by Filter: 1075812
|
Buffers: shared hit=9960 read=121303
|
Planning Time: 0.428 ms
|
Execution Time: 10779.613 ms
|

-- letter 'A' like expression index scan fast
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active and fi.relpath like 'A%' limit 1;
QUERY PLAN
|
-------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.55..60.85 rows=1 width=8) (actual time=7.047..7.048 rows=0
loops=1) |
Output: id
|
Buffers: shared hit=2776
|
-> Index Scan using test on media.oo_file fi (cost=0.55..4583.29
rows=76 width=8) (actual time=7.045..7.045 rows=0 loops=1)|
Output: id
|
Index Cond: ((fi.relpath >= 'A'::text) AND (fi.relpath <
'B'::text)) |
Filter: (fi.is_active AND (fi.relpath ~~ 'A%'::text))
|
Rows Removed by Filter: 3784
|
Buffers: shared hit=2776
|
Planning Time: 0.937 ms
|
Execution Time: 7.091 ms
|

-- letter 'T' like expression, seq scan slow
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active and fi.relpath like 'Természettudomány%' limit 1;
QUERY PLAN
|
-----------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.00..2.70 rows=1 width=8) (actual time=9842.935..9842.938
rows=0 loops=1) |
Output: id
|
Buffers: shared hit=10024 read=121239
|
-> Seq Scan on media.oo_file fi (cost=0.00..144710.65 rows=53574
width=8) (actual time=9842.933..9842.934 rows=0 loops=1)|
Output: id
|
Filter: (fi.is_active AND (fi.relpath ~~
'Természettudomány%'::text))
|
Rows Removed by Filter: 1075812
|
Buffers: shared hit=10024 read=121239
|
Planning Time: 0.975 ms
|
Execution Time: 9842.962 ms
|