From: Tom Lane <tgl@sss.pgh.pa.us>
To: Benjamin Arai <benjamin@araisoft.com>
Cc: PostgreSQL <pgsql-general@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow TSearch2 performance for table with 1 million documents.
Date: Fri, 05 Oct 2007 11:12:45 -0400
Message-ID: <16663.1191597165@sss.pgh.pa.us> (raw)
In-Reply-To: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com>
References: <D582BC3F-9F49-40C9-A959-1F9C8D3AA60B@araisoft.com>
Benjamin Arai <benjamin@araisoft.com> writes:
> # 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)
The time seems all spent at the join step, which is odd because it
really hasn't got much to do. AFAICS all it has to do is compute the
rank() values that the sort step will use. Is it possible that
rank() is really slow?
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, benjamin@araisoft.com, pgsql-general@postgresql.org
Subject: Re: Slow TSearch2 performance for table with 1 million documents.
In-Reply-To: <16663.1191597165@sss.pgh.pa.us>
* 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