From: Benjamin Arai <benjamin@araisoft.com>
To: PostgreSQL <pgsql-general@postgresql.org>
To: pgsql-performance@postgresql.org
Subject: Slow TSearch2 performance for table with 1 million documents.
Date: Fri, 5 Oct 2007 00:50:17 -0700
Message-ID: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com> (raw)
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;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------------
Sort (cost=6576.74..6579.07 rows=933 width=774) (actual
time=12969.237..12970.490 rows=5119 loops=1)
Sort Key: rank(fulltext_article.idxfti, q.q)
-> Nested Loop (cost=3069.79..6530.71 rows=933 width=774)
(actual time=209.513..12955.498 rows=5119 loops=1)
-> Function Scan on q (cost=0.00..0.01 rows=1 width=32)
(actual time=0.005..0.006 rows=1 loops=1)
-> Bitmap Heap Scan on fulltext_article
(cost=3069.79..6516.70 rows=933 width=742) (actual
time=209.322..234.390 rows=5119 loops=1)
Recheck Cond: (fulltext_article.idxfti @@ q.q)
-> Bitmap Index Scan on fulltext_article_idxfti_idx
(cost=0.00..3069.56 rows=933 width=0) (actual time=208.373..208.373
rows=5119 loops=1)
Index Cond: (fulltext_article.idxfti @@ q.q)
Total runtime: 12973.035 ms
(9 rows)
# select count(*) from fulltext_article;
count
--------
933001
(1 row)
# select COUNT(*) FROM fulltext_article, to_tsquery('simple','blue &
green') AS q WHERE idxfti @@ q;
count
-------
6308
(1 row)
Benjamin
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: benjamin@araisoft.com, pgsql-general@postgresql.org
Subject: Re: Slow TSearch2 performance for table with 1 million documents.
In-Reply-To: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com>
References: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com>
* 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