Slow TSearch2 performance for table with 1 million documents. - Mailing list pgsql-performance

From Benjamin Arai
Subject Slow TSearch2 performance for table with 1 million documents.
Date
Msg-id D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com
Whole thread Raw
Responses Re: [GENERAL] Slow TSearch2 performance for table with 1 million documents.
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Chris
Date:
Subject: Re: Partitioning in postgres - basic question
Next
From: Cláudia Macedo Amorim
Date:
Subject: Problems with + 1 million record table