Benki → Post #823

This website now has a search bar.

Since I am using PostgreSQL, which has a basic form of full text indexing built in, it was pretty easy to implement. You can find the implementation on Gerrit.

One interesting question was how to integrate a clause using the full-text search operator @@ into a Criteria query. I experimented a bit and found that if you define an IMMUTABLE function and use it in a query, PostgreSQL has no trouble inlining and optimizing it, so it’s a great way to call into PostgreSQL-specific functionality from within a Criteria query. For instance, it will make perfectly fine use of indices where possible:

mulkcms=# EXPLAIN
            SELECT cached_description_html 
              FROM benki.bookmark_texts
             WHERE post_matches_websearch(search_terms, 'en', 'test');

                        QUERY PLAN
---------------------------------------------------------------
 Bitmap Heap Scan on bookmark_texts
   Recheck Cond: (search_terms @@ '''test'''::tsquery)
   ->  Bitmap Index Scan on bookmark_texts_search_terms_idx
         Index Cond: (search_terms @@ '''test'''::tsquery)

Just as you expect from a high-quality database system.