pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to speed up word count in tsearch2?
4+ messages / 3 participants
[nested] [flat]

* How to speed up word count in tsearch2?
@ 2005-03-31 19:58  Yudie Pg <yudiepg@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Yudie Pg @ 2005-03-31 19:58 UTC (permalink / raw)
  To: 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?



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to speed up word count in tsearch2?
@ 2005-03-31 20:50  Josh Berkus <josh@agliodbs.com>
  parent: Yudie Pg <yudiepg@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Josh Berkus @ 2005-03-31 20:50 UTC (permalink / raw)
  To: Yudie Pg <yudiepg@gmail.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to speed up word count in tsearch2?
@ 2005-04-01 04:03  Yudie Pg <yudiepg@gmail.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Yudie Pg @ 2005-04-01 04:03 UTC (permalink / raw)
  To: pgsql-performance

> 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?



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to speed up word count in tsearch2?
@ 2005-04-01 12:17  Mike Rylander <mrylander@gmail.com>
  parent: Yudie Pg <yudiepg@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Mike Rylander @ 2005-04-01 12:17 UTC (permalink / raw)
  To: Yudie Pg <yudiepg@gmail.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-04-01 12:17 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-31 19:58 How to speed up word count in tsearch2? Yudie Pg <yudiepg@gmail.com>
2005-03-31 20:50 ` Josh Berkus <josh@agliodbs.com>
2005-04-01 04:03   ` Yudie Pg <yudiepg@gmail.com>
2005-04-01 12:17     ` Mike Rylander <mrylander@gmail.com>

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