From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Les <nagylzs@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Terribly slow query with very good plan?
Date: Fri, 04 Feb 2022 10:18:43 +0100
Message-ID: <64f14cb61b82fc8bd2d03963d03b7da252b1ba59.camel@cybertec.at> (raw)
In-Reply-To: <CAKXe9UAFjb84Hop=SAf65ni9Km5nCgpU8bbwfaEcaq6DbxW2Ew@mail.gmail.com>
References: <CAKXe9UAFjb84Hop=SAf65ni9Km5nCgpU8bbwfaEcaq6DbxW2Ew@mail.gmail.com>
On Fri, 2022-02-04 at 10:11 +0100, Les wrote:
> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
>
> select id, title,
> (exists (select 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, 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 |
> Planning Time: 0.361 ms |
> Execution Time: 25415.088 ms |
>
> It also returns 45 rows, but in 25 seconds which is unacceptable.
You should create an index that supports LIKE; for example
CREATE INDEX ON media.oo_file (relpath COLLATE "C");
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, nagylzs@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Terribly slow query with very good plan?
In-Reply-To: <64f14cb61b82fc8bd2d03963d03b7da252b1ba59.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox