I have a really slow query(~30 seconds) on a large text search DB that I could really use some help with.
I'm searching for ordered text, in a long file path string. So the path could be:
folder1/cat 123/dog 234
ilike '%search_term_1% %search_term_2%' to do searches. So
cat dog would return the result above.
I have a table with two columns:
create table file ( id bigserial primary key, path varchar(2048) not null, peers integer);
I want to do a text search on
path, and order by
peers desc nulls last
I've created the following two indices to speed up the queries:
create index idx_file_path_tri on file using gin (path gin_trgm_ops); create index idx_file_peers on file(peers nulls last);
explain anaylze results:
explain analyze select * from file where path ilike '%cat%' order by peers desc nulls last limit 15;
It looks like I need a compound index with the gin, but it won't let me build one based on
peers desc nulls last...
Limit (cost=7512.09..7512.13 rows=15 width=126) (actual time=342729.147..342729.153 rows=15 loops=1) -> Sort (cost=7512.09..7516.48 rows=1753 width=126) (actual time=342729.144..342729.145 rows=15 loops=1) Sort Key: peers DESC NULLS LAST Sort Method: top-N heapsort Memory: 27kB -> Bitmap Heap Scan on file (cost=809.59..7469.09 rows=1753 width=126) (actual time=2143.088..342610.395 rows=219 580 loops=1) Recheck Cond: ((path)::text ~~* '%cat%'::text) Heap Blocks: exact=38190 -> Bitmap Index Scan on idx_file_path_tri (cost=0.00..809.15 rows=1753 width=0) (actual time=2108.286..2108 .286 rows=223590 loops=1) Index Cond: ((path)::text ~~* '%cat%'::text) Planning time: 0.328 ms Execution time: 342729.330 ms (11 rows)
Note: the queries go extremely quickly without the