Postgres Full Text Search performance question

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)

Total 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!

Magnus Hagander · Accepted Answer · 2010-11-16 19:50:20Z

2

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.

answered Nov 16, 2010 at 19:50

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kertal Over a year ago

since 9.1 is out now, but i can't find any info about ispell speed, are there any improvments? thanx!

Collectives™ on Stack Overflow

Postgres Full Text Search performance question

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related