Re: order by slowing down a query by 80 times

Rajesh Kumar Mallah wrote:

Dear List,

just by removing the order by co_name reduces the query time dramatically
from  ~ 9 sec  to 63 ms. Can anyone please help.

The 63 ms query result is probably useless since it returns a limit of 25 rows from an unordered result. It is not surprising that this is fast.

The pain is here:

Index Scan using profile_master_co_name on profile_master b  (cost=0.00..1125295.59 rows=6968 width=25) (actual time=0.097..9193.154 rows=2212 loops=1)
                               Filter: ((co_name IS NOT NULL) AND ((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text)))

It looks like seq_scans are disabled, since the index scan has only a filter expression but not an index cond.

regards,
Yeb Havinga

Regds
Rajesh Kumar Mallah.