Postgres Full Text Search performance question

Postgres Full Text Search performance seems to depend on the dictionary you use, here's an example

my table contains 60000 entries

default german dictionary (only stopwords, i think)

SELECT title FROM sitesearch s, to_tsquery('german','holz') query WHERE query @@ searchtext LIMIT 10

QUERY PLAN

Limit  (cost=247.14..282.99 rows=10 width=21) (actual time=1.286..1.534 rows=10 loops=1)
  ->  Nested Loop  (cost=247.14..1358.57 rows=310 width=21) (actual time=1.278..1.512 rows=10 loops=1)
        ->  Function Scan on query  (cost=0.00..0.01 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=1)
        ->  Bitmap Heap Scan on sitesearch s  (cost=247.14..1354.68 rows=310 width=570) (actual time=1.237..1.452 rows=10 loops=1)
              Recheck Cond: (query.query @@ s.searchtext)
              ->  Bitmap Index Scan on sitesearch_searchtext_idx  (cost=0.00..247.06 rows=310 width=0) (actual time=0.871..0.871 rows=1144 loops=1)
                    Index Cond: (query.query @@ s.searchtext)
Total runtime: 1.815 ms
8 row(s)

Total runtime: 13.414 ms

that's pretty fast

my dictionary german ispell

CREATE TEXT SEARCH DICTIONARY pg_german (
    TEMPLATE = ispell,
    DictFile = german,
    AffFile = german,
    StopWords = german
);

support for compound words enabled

dictfile: 319018 words

affix file: 1290 lines

stopwords: 264 words

SELECT title FROM sitesearch s, to_tsquery('public.pg_german','holz') query WHERE query @@ searchtext LIMIT 10

query plan

Limit  (cost=247.14..282.99 rows=10 width=21) (actual time=1.263..1.578 rows=10 loops=1)
  ->  Nested Loop  (cost=247.14..1358.57 rows=310 width=21) (actual time=1.255..1.556 rows=10 loops=1)
        ->  Function Scan on query  (cost=0.00..0.01 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)
        ->  Bitmap Heap Scan on sitesearch s  (cost=247.14..1354.68 rows=310 width=570) (actual time=1.229..1.495 rows=10 loops=1)
              Recheck Cond: (query.query @@ s.searchtext)
              ->  Bitmap Index Scan on sitesearch_searchtext_idx  (cost=0.00..247.06 rows=310 width=0) (actual time=0.896..0.896 rows=1144 loops=1)
                    Index Cond: (query.query @@ s.searchtext)
Total runtime: 1.818 ms
8 row(s)

Tot开发者_C百科al runtime: 1,520.428 ms

query plan is fast ... 1,5s total runtime? does it take that long to load / initialize the dictionary? How to speed up this process?

thanks!

---

Yes, ispell dictionaries are very slow to load the first time. If you're using those, you really need to use connection pooling - they only take the time the first time they are loaded for each connection.

There's work being done on it for 9.1, but for current versions you're stuck with that requirement.