From: | Ninad Shah <ninad(dot)shah(at)percona(dot)com> |
---|---|
To: | Les <nagylzs(at)gmail(dot)com> |
Cc: | Nick Cleaton <nick(at)cleaton(dot)net>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Terribly slow query with very good plan? |
Date: | 2022-02-04 13:33:16 |
Message-ID: | CAMtEjOZ70pJWbTabTvHnX9njLMUEZFHemCSnDamc3L31Gu_svw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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-text
search category; in such scenarios, b-tree may not be effective every time.
Hence, it may not deliver the result in the expected time-frame. I
recommend you to try creating a Gist or a GIN index here.
Regards,
Ninad
On Fri, Feb 4, 2022 at 6:52 PM Les <nagylzs(at)gmail(dot)com> wrote:
>
> >
>> > 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' limit 1;
> QUERY PLAN
> |
>
> --------------------------------------------------------------------------------------------------------------------------+
> Limit (cost=0.00..1904.09 rows=1 width=8) (actual time=3837.338..3837.340
> rows=0 loops=1) |
> Output: id
> |
> Buffers: shared hit=9355 read=121908
> |
> -> Seq Scan on media.oo_file fi (cost=0.00..144710.65 rows=76 width=8)
> (actual time=3837.336..3837.336 rows=0 loops=1)|
> Output: id
> |
> Filter: (fi.is_active AND ((fi.relpath)::text ^@ 'A'::text))
> |
> Rows Removed by Filter: 1075812
> |
> Buffers: shared hit=9355 read=121908
> |
> Planning Time: 0.391 ms
> |
> Execution Time: 3837.364 ms
> |
>
From | Date | Subject | |
---|---|---|---|
Next Message | Nick Cleaton | 2022-02-04 13:57:08 | Re: Terribly slow query with very good plan? |
Previous Message | Les | 2022-02-04 13:21:51 | Re: Terribly slow query with very good plan? |