| From: | Les <nagylzs(at)gmail(dot)com> |
|---|---|
| To: | Nick Cleaton <nick(at)cleaton(dot)net> |
| Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Terribly slow query with very good plan? |
| Date: | 2022-02-04 10:05:13 |
| Message-ID: | CAKXe9UB154FFMT2zmHhYOuD1wH4oyceBE3PTvrbx1-W+R_gg_A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
Nick Cleaton <nick(at)cleaton(dot)net> ezt írta (időpont: 2022. febr. 4., P,
11:00):
>
> 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.
>
>
First of all, it CANNOT start with '%'. This is a fact and this fact can be
determined by analyzing the query. Something that the query planner should
do, right?
Second argument: the same query is also slow with the ^@ operator...
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select id, title,
(exists (select f2.id from
media.oo_file f2
where f2.relpath ^@ f.relpath )) as has_file
from media.oo_folder f where f.parent_id is null
QUERY PLAN
|
--------------------------------------------------------------------------------------------------------------------------------------------------+
Index Scan using oo_folder_idx_parent on media.oo_folder f
(cost=0.42..449.38 rows=20 width=26) (actual time=1652.624..61636.232
rows=45 loops=1)|
Output: f.id, f.title, (SubPlan 1)
|
Index Cond: (f.parent_id IS NULL)
|
Buffers: shared hit=6672274
|
SubPlan 1
|
-> Index Only Scan using test on media.oo_file f2
(cost=0.55..98067.11 rows=5379 width=0) (actual time=1369.665..1369.665
rows=0 loops=45) |
Filter: (f2.relpath ^@ f.relpath)
|
Rows Removed by Filter: 777428
|
Heap Fetches: 736418
|
Buffers: shared hit=6672234
|
Planning Time: 0.346 ms
|
Execution Time: 61636.319 ms
|
> 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.
>
Forgive me, I typed in these examples for demonstration. The actual code
uses relpath || '/%' and it avoids those cases.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Les | 2022-02-04 10:09:52 | Re: Terribly slow query with very good plan? |
| Previous Message | Les | 2022-02-04 10:00:47 | Re: Terribly slow query with very good plan? |