Обсуждение: How to speed up word count in tsearch2?
(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?
Yudie, > (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. Yes, this is because your tsearch2 index is getting pushed out of RAM. When the index is cached it's very, very fast but takes a long time to get loaded from disk. You need to look at what else is using RAM on that machine. And maybe buy more. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
> You need to look at what else is using RAM on that machine. And maybe buy > more. Ouch.. I had that feeling also. then how can I know how much memory needed for certain amount words? and why counting uncommon words are faster than common one?
On Apr 1, 2005 4:03 AM, Yudie Pg <yudiepg@gmail.com> wrote: > > You need to look at what else is using RAM on that machine. And maybe buy > > more. > > Ouch.. I had that feeling also. then how can I know how much memory > needed for certain amount words? and why counting uncommon words are > faster than common one? Because the index is a tree. You fall of the end of a branch faster with uncommon words. Plus the executor goes back to the table for fewer real rows with uncommon words. It sounds like you may just need a faster disk subsystem. That would shrink the time for the first query on any particular set of words, and it would make everything else faster as a nice side effect. What does your disk layout look like now? -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org