From: | Nick Cleaton <nick(at)cleaton(dot)net> |
---|---|
To: | Les <nagylzs(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Terribly slow query with very good plan? |
Date: | 2022-02-04 09:59:55 |
Message-ID: | CAFgz3ku06QqTHz_FFnGLfcpy0OwwT7Pj_h=r1H_XLwWmv+3OZA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, 4 Feb 2022 at 09:11, Les <nagylzs(at)gmail(dot)com> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Les | 2022-02-04 10:00:47 | Re: Terribly slow query with very good plan? |
Previous Message | Les | 2022-02-04 09:32:58 | Re: Terribly slow query with very good plan? |