That may be because it's expecting to get 88290 rows from the
sequential scan, and the"limit 1" means it expects sequential scan to
be fast because on average it will only need to scan 1/88290 of the
table before it finds a matching row, then it can stop.
We are looking for a single row. With an index scan, it is always much faster to find a single row. No seq scan can be faster "on average", when you are looking for a single row. Am I wrong?
> Try it without the "limit 1"
Without the limit it uses bitmap heap scan. Unbelievable!
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");
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------+
Bitmap Heap Scan on media.oo_file fi (cost=10480.10..140065.96 rows=70010 width=8) (actual time=9757.917..9757.920 rows=0 loops=1) |
Output: id |
Recheck Cond: ((fi.relpath >= 'Természettudomány'::text COLLATE "C") AND (fi.relpath <= 'Természettudomány '::text COLLATE "C")) |
Filter: fi.is_active |
Rows Removed by Filter: 85207 |
Heap Blocks: exact=24954 |
Buffers: shared hit=197 read=26531 |
-> Bitmap Index Scan on test (cost=0.00..10462.59 rows=99404 width=0) (actual time=425.571..425.572 rows=85207 loops=1) |
Index Cond: ((fi.relpath >= 'Természettudomány'::text COLLATE "C") AND (fi.relpath <= 'Természettudomány '::text COLLATE "C"))|
Buffers: shared hit=6 read=1768 |
Planning Time: 1.145 ms |
JIT: |
Functions: 6 |
Options: Inlining false, Optimization false, Expressions true, Deforming true |
Timing: Generation 2.295 ms, Inlining 0.000 ms, Optimization 1.142 ms, Emission 11.632 ms, Total 15.070 ms |
Execution Time: 9760.361 ms |
--------------------------------------------------------------------------------------------------------------------------------------+
Bitmap Heap Scan on media.oo_file fi (cost=10480.10..140065.96 rows=70010 width=8) (actual time=9757.917..9757.920 rows=0 loops=1) |
Output: id |
Recheck Cond: ((fi.relpath >= 'Természettudomány'::text COLLATE "C") AND (fi.relpath <= 'Természettudomány '::text COLLATE "C")) |
Filter: fi.is_active |
Rows Removed by Filter: 85207 |
Heap Blocks: exact=24954 |
Buffers: shared hit=197 read=26531 |
-> Bitmap Index Scan on test (cost=0.00..10462.59 rows=99404 width=0) (actual time=425.571..425.572 rows=85207 loops=1) |
Index Cond: ((fi.relpath >= 'Természettudomány'::text COLLATE "C") AND (fi.relpath <= 'Természettudomány '::text COLLATE "C"))|
Buffers: shared hit=6 read=1768 |
Planning Time: 1.145 ms |
JIT: |
Functions: 6 |
Options: Inlining false, Optimization false, Expressions true, Deforming true |
Timing: Generation 2.295 ms, Inlining 0.000 ms, Optimization 1.142 ms, Emission 11.632 ms, Total 15.070 ms |
Execution Time: 9760.361 ms |
