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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
