Look into running Swish-e instead: http://www.swish-e.org
Great speed, nice engine, excellent boolean searches. We run it on several sites each with over 500,000 documents. Performance is consistently sub-second response time, and we also integrate it within PHP, Perl and Postgresql too.
I know, it is nice to use tsearch2, but we also found the performance lacking for those big indices. Maybe Oleg and the tsearch2 gang have some extra tips?
- Ericson
Bill Moran wrote:
Diogo Biazus wrote:
Hi folks,
I have a database using tsearch2 to index 300 000 documents.
I've already have optimized the queries, and the database is vacuumed on a daily basis.
The stat function tells me that my index has aprox. 460 000 unique words (I'm using stemmer and a nice stopword list).
The problem is performance, some queries take more than 10 seconds to execute, and I'm not sure if my bottleneck is memory or io.
The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running postgresql 7.4.3 over freebsd 5.0 with lots of shared buffers and sort_mem...
Does anyone has an idea of a more cost eficient solution?
How to get a better performance without having to invest some astronomicaly high amount of money?
This isn't hardware related, but FreeBSD 5 is not a particularly impressive
performer. Especially 5.0 ... 5.2.1 would be better, but if you're shooting
for performance, 4.9 will probably outperform both of them at this stage of
the game.
Something to consider if the query tuning that others are helping with doesn't
solve the problem. Follow through with that _first_ though.
However, if you insist on running 5, make sure your kernel is compiled without
WITNESS ... it speeds things up noticably.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
