Search Postgresql Archives

Slow TSearch2 performance for table with 1 million documents.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux