Re: order by slowing down a query by 80 times

[Rajesh Kumar Mallah <mallah.rajesh@xxxxxxxxx>]

On Mon, Jun 28, 2010 at 5:09 PM, Yeb Havinga <yebhavinga@xxxxxxxxx> wrote:

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.

seq_scans is NOT explicitly disabled. The two queries just differed in the order by clause.

regds
Rajesh Kumar Mallah.
 

regards,
Yeb Havinga

Regds
Rajesh Kumar Mallah.