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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
