The way to make this go faster is to set up the actually recommended infrastructure for full text search, namely create an index on (co_name_vec)::tsvector (either directly or using an auxiliary tsvector column). If you don't want to maintain such an index, fine, but don't expect full text search queries to be quick.
regards, tom lane
Dear Tom/List ,
co_name_vec is actually the auxiliary tsvector column that is mantained via a an update trigger. and the index that you suggested is there . consider simplified version. When we order by co_name the index on co_name_vec is not used some other index is used.
tradein_clients=> explain analyze SELECT profile_id from general.profile_master b where 1=1 and co_name_vec @@ to_tsquery ('manufacturer') order by co_name limit 25; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3958.48 rows=25 width=25) (actual time=0.045..19.847 rows=25 loops=1) -> Index Scan using profile_master_co_name on profile_master b (cost=0.00..1125315.59 rows=7107 width=25) (actual time=0.043..19.818 rows=25 loops=1) Filter: ((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text)) Total runtime: 19.894 ms (4 rows)
tradein_clients=> explain analyze SELECT profile_id from general.profile_master b where 1=1 and co_name_vec @@ to_tsquery ('manufacturer') limit 25; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..101.18 rows=25 width=4) (actual time=0.051..0.632 rows=25 loops=1) -> Index Scan using profile_master_co_name_vec on profile_master b (cost=0.00..28761.89 rows=7107 width=4) (actual time=0.049..0.593 rows=25 loops=1) Index Cond: ((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text)) Total runtime: 0.666 ms (4 rows)
