How to speed up word count in tsearch2?

From: Yudie Pg <yudiepg(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: How to speed up word count in tsearch2?
Date: 2005-03-31 19:58:30
Message-ID: e460d0c05033111584290bc6c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

(It is the 2nd posting, maybe the 1st one didn't goes thru)
I've tested several keyword count from 2 millions record book
description table that indexed with tseach2 indexing.
The result is always slow for first query attempt.

This my sample query:
-- test one phrase --
SELECT count(*) from table1
WHEREsearchvector @@ to_tsquery('default' ,'david') limit 100
:: returns 16824 records match.
:: take 49618.341 ms (1st attempt)
:: take 504.229 ms (2nd attempt)

-- test two phrase --
SELECT count(*) from table1
WHERE searchvector @@ to_tsquery('default' ,'martha&stewart') limit 100
:: returns 155 records match.
:: take 686.669 ms (1st attempt)
:: take 40.282 ms (2nd attempt)

I use ordinary aggregate function count(*), Is there other way to count faster?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2005-03-31 20:50:45 Re: How to speed up word count in tsearch2?
Previous Message Madison Kelly 2005-03-31 19:51:51 Re: Very long time to execute and Update, suggestions?