Message-ID: <5075BDB2.2030507@hathawaymix.org>
Date: Wed, 10 Oct 2012 12:25:54 -0600
From: Shane Hathaway <shane@hathawaymix.org>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:15.0) Gecko/20120912 Thunderbird/15.0.1
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Fran=E7ois_Beausoleil?= <francois@teksol.info>
CC: pgsql-performance@postgresql.org
Subject: Re: Ways to speed up ts_rank
References: <5074996C.5020809@hathawaymix.org>
	<546C964C-5BE7-464D-9EC6-5FC816AEE65E@teksol.info>
In-Reply-To: <546C964C-5BE7-464D-9EC6-5FC816AEE65E@teksol.info>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

On 10/10/2012 06:38 AM, François Beausoleil wrote:
> Notice the ts_rank() is on the outer query, which means it'll only
> run on the subset of documents which match the query. This is
> explicitly mentioned in the docs:
>
> """Ranking can be expensive since it requires consulting the tsvector
> of each matching document, which can be I/O bound and therefore slow.
> Unfortunately, it is almost impossible to avoid since practical
> queries often result in large numbers of matches."""
>
> (last paragraph of)
> http://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-RANKING

Indeed, I have studied that paragraph in depth, trying to gather as much 
possible meaning from it as I can. :-)

However, the following two queries take exactly the same time, 
suggesting to me that ts_rank_cd is really only looking at matching 
rows, not all rows:

SELECT docid, coefficient * ts_rank_cd('{0.1, 0.2, 0.5, 1.0}',
               text_vector, to_tsquery('english', 'stuff')) AS rank
FROM pgtextindex
WHERE (text_vector @@ to_tsquery('english', 'stuff'))
ORDER BY rank DESC
limit 3;

SELECT docid, coefficient * ts_rank_cd('{0.1, 0.2, 0.5, 1.0}',
               text_vector, to_tsquery('english', 'stuff')) AS rank
FROM (SELECT * FROM pgtextindex
       WHERE (text_vector @@ to_tsquery('english', 'stuff'))) AS filtered
ORDER BY rank DESC
limit 3;

Thanks for the suggestion though.  By the way, all the tsvectors are 
already loaded into the kernel cache when I execute the queries, so 
ranking large documents is in fact CPU bound rather than I/O bound.  The 
CPU is pegged for the whole time.

Shane