Re: order by slowing down a query by 80 times

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)

tradein_clients=>