pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / 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