Re: Terribly slow query with very good plan?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, 4 Feb 2022 at 09:11, Les <nagylzs@xxxxxxxxx> wrote:

                                                                    |
>     ->  Index Only Scan using oo_file_idx_relpath on media.oo_file f2  (cost=0.55..108499.27 rows=5381 width=0) (actual time=564.756..564.756 rows=0 loops=45)|
>           Filter: (f2.relpath ~~ (f.relpath || '%'::text))                                                                                                    |
>           Rows Removed by Filter: 792025                                                                                                                      |
>           Heap Fetches: 768960                                                                                                                                |
>           Buffers: shared hit=7014130                                                                                                                         |
> Planning Time: 0.361 ms
> Execution Time: 25415.088 ms


>     ->  Seq Scan on media.oo_file of2  (cost=0.00..144714.70 rows=86960 width=0) (actual time=0.044..0.044 rows=1 loops=1)|
>           Filter: (of2.relpath ~~ 'Felhasználók%'::text)                                                                  |
>           Rows Removed by Filter: 15                                                                                      |
>           Buffers: shared hit=2                                                                                           |
> Planning Time: 0.290 ms                                                                                                   |
> Execution Time: 0.076 ms                                                                                                  |
>
> In other words, I could write a pl/sql function with a nested loop instead of the problematic query, and it will be 1000 times faster.
>
> What am I missing?

In the fast case the 'Felhasználók%' part is known at query planning
time, so it can be a prefix search.

In the slow case, the planner doesn't know what that value will be, it
could be something that starts with '%' for example.

Also your logic looks a bit unsafe, the query you have would include
files under all top-level folders with names starting with
Felhasználók, so you could accidentally merge in files in folders
called Felhasználókfoo and Felhasználókbar for example.






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux