Re: Terribly slow query with very good plan?

> Slow
>
> What about this:
>
> fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C")
>

It uses index scan.

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active
and fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C")
limit 1;
QUERY PLAN
|
---------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.55..6.12 rows=1 width=8) (actual time=1623.069..1623.070
rows=0 loops=1) |
Output: id
|
Buffers: shared hit=2439 read=1994 dirtied=1107
|
-> Index Scan using test on media.oo_file fi (cost=0.55..5732.47
rows=1029 width=8) (actual time=1623.067..1623.067 rows=0 loops=1)|
Output: id
|
Index Cond: ((fi.relpath >= 'A'::text COLLATE "C") AND (fi.relpath
<= 'A '::text COLLATE "C")) |
Filter: fi.is_active
|
Rows Removed by Filter: 3784
|
Buffers: shared hit=2439 read=1994 dirtied=1107
|
Planning Time: 18.817 ms
|
Execution Time: 1623.104 ms
|

Although the same with 'Természettudomány' uses seq scan:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active
and fi.relpath between
('Természettudomány' collate "C")
and ('Természettudomány'||chr(255) collate "C")
limit 1;

QUERY PLAN
|
---------------------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.00..2.13 rows=1 width=8) (actual time=7521.531..7521.532
rows=0 loops=1) |
Output: id
|
Buffers: shared hit=17018 read=150574
|
-> Seq Scan on media.oo_file fi (cost=0.00..188195.39 rows=88290
width=8) (actual time=7521.528..7521.529 rows=0 loops=1)
|
Output: id
|
Filter: (fi.is_active AND (fi.relpath >= 'Természettudomány'::text
COLLATE "C") AND (fi.relpath <= 'Természettudomány '::text COLLATE "C"))|
Rows Removed by Filter: 1075812
|
Buffers: shared hit=17018 read=150574
|
Planning Time: 8.918 ms
|
Execution Time: 7521.560 ms
|