> 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;
---------------------------------------------------------------------------------------------------------------------------------------+
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 |
---------------------------------------------------------------------------------------------------------------------------------------------------+
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 |
