pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Large Text Search Help
2+ messages / 2 participants
[nested] [flat]

* Large Text Search Help
@ 2003-10-08 15:48 psql-mail@freeuk.com
  2003-10-14 17:12 ` Re: Large Text Search Help Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 2+ messages in thread

From: psql-mail@freeuk.com @ 2003-10-08 15:48 UTC (permalink / raw)
  To: pgsql-performance

Hi,
I am trying to design a large text search database.

It will have upwards of 6 million documents, along with meta data on 
each.

I am currently looking at tsearch2 to provide fast text searching and 
also playing around with different hardware configurations.

1. With tsearch2 I get very good query times up until I insert more 
records. For example with 100,000 records tsearch2 returns in around 6 
seconds, with 200,000 records tsearch2 returns in just under a minute. 
Is this due to the indices fitting entirely in memory with 100,000 
records?

2. As well as whole word matching i also need to be able to do 
substring matching. Is the FTI module the way to approach this?

3. I have just begun to look into distibuted queries. Is there an 
existing solution for distibuting a postgresql database amongst 
multiple servers, so each has the same schema but only a subset of the 
total data?

Any other helpful comments or sugestions on how to improve query times 
using different hardware or software techniques would be appreciated.

Thanks,

Mat



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

* Re: Large Text Search Help
  2003-10-08 15:48 Large Text Search Help psql-mail@freeuk.com
@ 2003-10-14 17:12 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2003-10-14 17:12 UTC (permalink / raw)
  To: psql-mail@freeuk.com; pgsql-performance

Mat,

> 1. With tsearch2 I get very good query times up until I insert more
> records. For example with 100,000 records tsearch2 returns in around 6
> seconds, with 200,000 records tsearch2 returns in just under a minute.
> Is this due to the indices fitting entirely in memory with 100,000
> records?

Maybe, maybe not.  If you want a difinitive answer, post your EXPLAIN ANALYZE 
results with the original query.  

I assume that you have run VACUUM ANALYZE, first?  Don't bother to respond 
until you have.

> 2. As well as whole word matching i also need to be able to do
> substring matching. Is the FTI module the way to approach this?

Yes.

> 3. I have just begun to look into distibuted queries. Is there an
> existing solution for distibuting a postgresql database amongst
> multiple servers, so each has the same schema but only a subset of the
> total data?

No, it would be ad-hoc.  So far, Moore's law has prevented us from needing to 
devote serious effort to the above approach.

> Any other helpful comments or sugestions on how to improve query times
> using different hardware or software techniques would be appreciated.

Read the archives of this list.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



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


end of thread, other threads:[~2003-10-14 17:12 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-08 15:48 Large Text Search Help psql-mail@freeuk.com
2003-10-14 17:12 ` Josh Berkus <josh@agliodbs.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