Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
> > That may be because it's expecting to get 88290 rows from the > sequential scan, and the"limit 1" means it expects sequential scan to > be fast because on average it will only need to scan 1/88290 of the > table before it finds a matching row, then it can stop. > We are looking for a single r

Re: Terribly slow query with very good plan?

2022-02-04 Thread Thomas Kellerer
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 from media.

Re: Terribly slow query with very good plan?

2022-02-04 Thread Nick Cleaton
On Fri, 4 Feb 2022 at 14:07, Les wrote: > > > >> > Slow >> >> What about this: >> >> fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C") > > > It uses index scan. > Although the same with 'Természettudomány' uses seq scan: > > > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) > select

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
> Slow > > What about this: > > fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C") > It uses index scan. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select fi.id from media.oo_file fi where fi.is_active and fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C") lim

Re: Terribly slow query with very good plan?

2022-02-04 Thread Nick Cleaton
On Fri, 4 Feb 2022 at 13:21, Les wrote: > >> What if you try applying the C collation to the values from the table: >> >> where fi.is_active and fi.relpath collate "C" ^@ 'A' > > > Slow What about this: fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C")

Re: Terribly slow query with very good plan?

2022-02-04 Thread Ninad Shah
Hi Les, I have reviewed the whole thread, and I do not see usage of gist or gin indexes. Have you tried using Gist or GIN indexes instead of a normal b-tree? B-trees are a good option when your search is simple(e.g. =, >, <). The operators you are using are "like" or "^@", which fall into a full-

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
> > > > It does not help. > > What if you try applying the C collation to the values from the table: > > where fi.is_active and fi.relpath collate "C" ^@ 'A' > Slow EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select fi.id from media.oo_file fi where fi.is_active and fi.relpath collate "C" ^@ 'A'

Re: Terribly slow query with very good plan?

2022-02-04 Thread Nick Cleaton
On Fri, 4 Feb 2022 at 13:07, Les wrote: > >> >> > PostgreSQL uses seq scan for O, F, N, T letters, but it uses index scan >> > for A, I, C letters (with the "like" query). >> >> That's interesting. >> >> Does it help if you create an additional index on relpath with the >> text_pattern_ops modifi

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
> > PostgreSQL uses seq scan for O, F, N, T letters, but it uses index scan > for A, I, C letters (with the "like" query). > > That's interesting. > > Does it help if you create an additional index on relpath with the > text_pattern_ops modifier, e.g. > > CREATE INDEX ... USING btree (relpath text_

Re: Terribly slow query with very good plan?

2022-02-04 Thread Nick Cleaton
On Fri, 4 Feb 2022 at 12:27, Les wrote: > PostgreSQL uses seq scan for O, F, N, T letters, but it uses index scan for > A, I, C letters (with the "like" query). That's interesting. Does it help if you create an additional index on relpath with the text_pattern_ops modifier, e.g. CREATE INDEX

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
Nick Cleaton ezt írta (időpont: 2022. febr. 4., P, 11:57): > > With the ^@ operator, my guess is that because the planner knows > nothing about the folder name value it could be the empty string, > which would be a prefix of everything. > I think I could narrow down the problem to the simplest q

Re: Terribly slow query with very good plan?

2022-02-04 Thread Nick Cleaton
On Fri, 4 Feb 2022 at 10:09, Les wrote: > > Oh I see, the query planner does not know that there will be no % characters > in file and folder names. > > But what is the solution then? It just seems wrong that I can speed up a > query 1000 times by replacing it with a nested loop in a pl/sql func

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
> > >> >> 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... >> > > Oh I see, the query planner does not kn

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
> 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

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
Nick Cleaton 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 exampl

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
I really think now that the query plan is wrong (or "could be improved" so to say). As far as I understand, the "index only scan" is essentially a sequential scan on the index data. In this specific case, where the filter is a "begins with" condition on a field that is the starting (and only) colum

Re: Terribly slow query with very good plan?

2022-02-04 Thread Nick Cleaton
On Fri, 4 Feb 2022 at 09:11, Les 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:

Re: Terribly slow query with very good plan?

2022-02-04 Thread Les
Laurenz Albe ezt írta (időpont: 2022. febr. 4., P, 10:18): > | > > > > 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 (relp

Re: Terribly slow query with very good plan?

2022-02-04 Thread Pavel Stehule
pá 4. 2. 2022 v 10:11 odesílatel Les napsal: > Hello, > > I have a table that contains folders, and another one that contains files. > > Here are the table definitions. I have removed most of the columns because > they are not important for this question. (There are lots of columns.) > > CREATE T

Re: Terribly slow query with very good plan?

2022-02-04 Thread Laurenz Albe
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                

Terribly slow query with very good plan?

2022-02-04 Thread Les
Hello, I have a table that contains folders, and another one that contains files. Here are the table definitions. I have removed most of the columns because they are not important for this question. (There are lots of columns.) CREATE TABLE media.oo_folder ( id int8 NOT NULL, is_active bool NOT