From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Terribly slow query with very good plan? |
Date: | 2022-02-04 14:42:37 |
Message-ID: | 03b5e866-b1c9-8f11-c34c-5b47a043671f@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Les schrieb am 04.02.2022 um 10:11:
> My task is to write a query that tells if a folder has any active file inside it - directly or in subfolders. Here is the query for that:
>
> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
>
> select id, title,
> (exists (select f2.id <http://f2.id> from media.oo_file f2 where f2.relpath like 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..488.02 rows=20 width=26) (actual time=713.419..25414.969 rows=45 loops=1) |
> Output: f.id <http://f.id>, f.title, (SubPlan 1) |
> Index Cond: (f.parent_id IS NULL) |
> Buffers: shared hit=7014170 |
> SubPlan 1 |
> -> 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 |
In addition to the collation tweaks, I wonder if using a lateral join might result in a more efficient plan:
select id, title, c.id is not null as has_path
from media.oo_folder f
left join lateral (
select f2.id
from media.oo_file f2
where f2.relpath like f.relpath || '%'
limit 1
) c on true
where f.parent_id is null
From | Date | Subject | |
---|---|---|---|
Next Message | Les | 2022-02-04 14:43:55 | Re: Terribly slow query with very good plan? |
Previous Message | Nick Cleaton | 2022-02-04 14:34:52 | Re: Terribly slow query with very good plan? |