From: Alban Hertroys <a.hertroys@magproductions.nl>
To: Benjamin Arai <benjamin@araisoft.com>
Cc: PostgreSQL <pgsql-general@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow TSearch2 performance for table with 1 million documents.
Date: Fri, 05 Oct 2007 14:00:59 +0200
Message-ID: <4706277B.6010000@magproductions.nl> (raw)
In-Reply-To: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com>
References: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com>
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 //
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a.hertroys@magproductions.nl, benjamin@araisoft.com, pgsql-general@postgresql.org
Subject: Re: Slow TSearch2 performance for table with 1 million documents.
In-Reply-To: <4706277B.6010000@magproductions.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox