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(dot)hertroys(at)magproductions(dot)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 //