Re: Terribly slow query with very good plan?

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

>
>