Re: Slow search.. quite clueless

Oleg Bartunov wrote:
> On Mon, 26 Sep 2005, Yonatan Ben-Nes wrote:
>
>> Hi again everyone,
>>
>> Oleg I tried tsearch2 and happily it does work wonderfully for me
>> returning results extremly fast and actually its working even better
>> then I wanted with all of those neat features like: lexem, weight &
>> stop words.
>>
>> I got only one problem which is when I want the results to be ordered
>> by a diffrent field (like print INT field) it takes quite alot of time
>> for it to do it if the query can return lots of results (for example
>> search for the word "computer") and thats even if I limit the results.
>> The best way to improve its speed for such quereies (that I've
>> found...) is to create an index on the field which I want to order by
>> and using it CLUSTER the table, after the clustering I drop the the
>> index so it won't be used when I run queries with ORDER BY on that
>> field, that seem to improve the speed, if anyone got a better idea ill
>> be glad to hear it.
>
>
> what's your actual query ?  have you tried multicolumn index ?
>
>
>>
>> Anyway thanks alot everyone!
>>  Ben-Nes Yonatan
>>
>
>     Regards,
>         Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83

Hi Oleg,

I can't use a multicolumn index cause I already use on that table the
tsearch2 index, here is the query:

EXPLAIN ANALYZE SELECT product_id,final_price FROM product WHERE
keywords_vector @@ to_tsquery('cat') ORDER BY retail_price LIMIT 13;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=4.02..4.03 rows=1 width=39) (actual time=367.627..367.654
rows=13 loops=1)
    ->  Sort  (cost=4.02..4.03 rows=1 width=39) (actual
time=367.622..367.630 rows=13 loops=1)
          Sort Key: retail_price
          ->  Index Scan using product_keywords_vector_idx on product
(cost=0.00..4.01 rows=1 width=39) (actual time=0.056..276.385 rows=14295
loops=1)
                Index Cond: (keywords_vector @@ '\'cat\''::tsquery)
  Total runtime: 370.916 ms
(6 rows)

Now this is the result after its already at the cache (made such a query
b4), the first time I ran this query it took few seconds...

Thanks as always :),
   Ben-Nes Yonatan