Re: Terribly slow query with very good plan?

On Fri, 4 Feb 2022 at 14:07, Les <nagylzs@gmail.com> wrote:
>
>
>
>> > Slow
>>
>> What about this:
>>
>> fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C")
>
>
> It uses index scan.

> 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

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.

Try it without the "limit 1"