CODEWITHSUNDEEP
postgresqlindexingpostgresql-9.5
dessalines
dessalines

Reputation: 7382

Postgres text search with order by / sorting

My use case, is I need to to a text search on a field, and then order by another column, unrelated to the text search, but I can't seem to create an index that handles both.

Create table:

create table file (
id bigint,
path character varying(2048),
peers bigint,
text_search tsvector
);

Some indices to test:

create index idx_file_text_search_1 on file using gin (text_search);
create index idx_file_text_search_2 on file using gin (peers, text_search);
create index idx_file_peers on file using btree (peers desc);

Here is my main query:

explain analyze 
select * 
from file_fast 
where text_search @@ to_tsquery('whatever') 
order by peers desc 
limit 10;

Yet its only using the peers index:

 Limit  (cost=0.43..20870.27 rows=10 width=316) (actual time=2507.304..9016.220 rows=10 loops=1)
   ->  Index Scan using idx_file_peers on file  (cost=0.43..18286146.09 rows=8762 width=316) (actual time=2507.301..9016.205 rows=10 loops=1)
         Filter: (text_search @@ to_tsquery('ole'::text))
         Rows Removed by Filter: 497504
 Planning time: 0.399 ms
 Execution time: 9016.265 ms
(6 rows)

And when I try it without the order by, it appears to use text searching index:

-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=104.15..143.54 rows=10 width=316) (actual time=76.949..76.977 rows=10 loops=1)
   ->  Bitmap Heap Scan on file  (cost=104.15..34612.36 rows=8762 width=316) (actual time=76.946..76.970 rows=10 loops=1)
         Recheck Cond: (text_search @@ to_tsquery('ole'::text))
         Heap Blocks: exact=10
         ->  Bitmap Index Scan on idx_file_text_search_1 (cost=0.00..101.96 rows=8762 width=0) (actual time=76.802..76.802 rows=515 loops=1)
               Index Cond: (text_search @@ to_tsquery('ole'::text))
 Planning time: 0.376 ms
 Execution time: 175.775 ms
(8 rows)

Does postgres really lack an index to be able to text search, and sort on another field?

Upvotes: 1

Views: 2342

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

dont know if you can improve the index but if second query is the faster one maybe you can split the query

with cte as (
    select * 
    from file_fast 
    where text_search @@ to_tsquery('whatever') 
)
SELECT *
FROM cte
order by peers desc 
limit 10;

Upvotes: 2

Related Questions