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 <nagy...@gmail.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 > | >
