Re: Slow TSearch2 performance for table with 1 million documents.

Benjamin Arai wrote:
> Hi,
>
> I have very slow performance for a TSearch2 table.  I have pasted the
> EXPLAIN ANALYZE queries below.  12 seconds is slow for almost any
> purpose.  Is there any way to speed this up?
>
> # explain analyze select * FROM fulltext_article,
> to_tsquery('simple','dog') AS q  WHERE idxfti @@ q ORDER BY rank(idxfti,
> q) DESC;

Admittedly I'm kind of new to tsearch, but wouldn't

SELECT *
  FROM fulltext_article
 WHERE idxfti @@ to_tsquery('simple','dog')
 ORDER BY rank(idxfti, to_tsquery('simple', 'dog')) DESC;

be faster?

Quick testing shows a similar query in our database to not use a nested
loop and a function scan. For comparison, here are our plans:

Your approach:

QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4.86..4.87 rows=1 width=164) (actual time=0.151..0.161
rows=5 loops=1)
   Sort Key: rank(fulltext_article.idxfti, q.q)
   ->  Nested Loop  (cost=0.00..4.85 rows=1 width=164) (actual
time=0.067..0.119 rows=5 loops=1)
         ->  Function Scan on q  (cost=0.00..0.01 rows=1 width=32)
(actual time=0.010..0.012 rows=1 loops=1)
         ->  Index Scan using fulltext_article_idxfti_idx on
fulltext_article  (cost=0.00..4.82 rows=1 width=132) (actual
time=0.033..0.056 rows=5 loops=1)
               Index Cond: (fulltext_article.idxfti @@ "outer".q)
               Filter: (fulltext_article.idxfti @@ "outer".q)
 Total runtime: 0.242 ms
(8 rows)


My suggested approach:

                                                                   QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4.84..4.84 rows=1 width=132) (actual time=0.085..0.095
rows=5 loops=1)
   Sort Key: rank(idxfti, '''dog'''::tsquery)
   ->  Index Scan using fulltext_article_idxfti_idx on fulltext_article
 (cost=0.00..4.83 rows=1 width=132) (actual time=0.025..0.052 rows=5
loops=1)
         Index Cond: (idxfti @@ '''dog'''::tsquery)
         Filter: (idxfti @@ '''dog'''::tsquery)
 Total runtime: 0.163 ms
(6 rows)

I hope this helps.

--
Alban Hertroys
a.hertroys@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //