pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (8+ messages)  latest in thread

Message-ID: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com>
Permalink:  ../D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com/
Also on:    postgresql.org/message-id/D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com

 · 

reply

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