OpenFTS

I have a trigger to automatically set a ts_vector column on inserts or 
updates, like this:

 CREATE TRIGGER jobs_ts_trg BEFORE UPDATE OR INSERT ON jobs
   FOR EACH ROW EXECUTE PROCEDURE tsearch2(ts, ftxt);

How can I specify which tsearch2 config to use by this trigger function? It 
seems to always use the default one for my locale, which is not what I need 
for this particular table/column.

Regards,
-- 
Lars Haugseth

I'm using tsearch2 for providing search to a web site of mine.  I need
to be able to highlight the search results in an entire document, not
just the headline of the doc.  At first I built my own highlighting
function, but I noticed that tsearch2 supports stemming, which was
beyond the capabilties of my primitive function.  Rather than code up
a stemming engine myself, I would like to use the exact code that
tsearch2 is using to search my text in the first place.

Is there a function that will do this for me?

Thanks,
Carl

I'd also like to know how to turn off stemming, so that I can search
for an exact word or phrase and only that word or phrase.  Is this
possible?  Is it easy?

Thanks,
Carl

Hello  :)

We have some problems with search queries that uses tsearch2 on our 
postgresql db. The simple SELECT COUNT(*) WHERE ts @@ to_ts(..) takes 
more then 10 seconds (for popuplar words it can even be 30 s)! Is there 
anything that can be do about it?


Some information on our platform:

1. version of postgresql, OS, kernel

  PostgreSQL 7.4.8 on i386-redhat-linux-gnu, compiled by GCC 
i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red Hat 3.4.3-22))


2. output of 'explain analyze select bla-bla-bla;'


explain analyze
select count(*)
from wyszukiwarka_tmp where ts_vector_szukaj @@ 
to_tsquery('default_polish','ustawa:ABC');

Aggregate
     (cost=811.17..811.17 rows=1 width=0)
     (actual time=70937.878..70937.879 rows=1 loops=1)

   ->  Index Scan using wyszukiwarka_tmp_idx
     on wyszukiwarka_tmp
      (cost=0.00..810.68 rows=197 width=0)
      (actual time=735.454..70920.452 rows=11402 loops=1)

         Index Cond: (ts_vector_szukaj @@ '\'ustawa\':ABC'::tsquery)
         Filter: (ts_vector_szukaj @@ '\'ustawa\':ABC'::tsquery)

Total runtime: 70937.980 ms



4. Does '1 returned row' is a right answer ?

   First query should return total records maching (so YES). The second 
should return top 50 (ordered by ts rank function).

5. how many unique words did you indexed ?
     ~60.000 (~270.000 records)

6. How did you configure tsearch2 (dicts,indexed lexems)

Our not optimized dict :)

     ts_name     |  tok_alias   |  dict_name
----------------+--------------+-------------
  default_polish | email        | {pl_ispell}
  default_polish | file         | {pl_ispell}
  default_polish | float        | {pl_ispell}
  default_polish | host         | {pl_ispell}
  default_polish | hword        | {pl_ispell}
  default_polish | int          | {pl_ispell}
  default_polish | lhword       | {pl_ispell}
  default_polish | lpart_hword  | {pl_ispell}
  default_polish | lword        | {pl_ispell}
  default_polish | nlhword      | {pl_ispell}
  default_polish | nlpart_hword | {pl_ispell}
  default_polish | nlword       | {pl_ispell}
  default_polish | part_hword   | {pl_ispell}
  default_polish | sfloat       | {pl_ispell}
  default_polish | uint         | {pl_ispell}
  default_polish | uri          | {pl_ispell}
  default_polish | url          | {pl_ispell}
  default_polish | version      | {pl_ispell}
  default_polish | word         | {pl_ispell}

As showed above some combinations (ie uri + ispell or float + ispell) 
are completly useless yet the dictionary size is not a matter of 
importance here (60.000 words for 260.000 records).


7. Does other query (non tsearch2) are slow ? This help to identify
    if you configure postgresql properly.

     Nope, other queries seem fast.


Best regards,
Wojciech