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.
-- Bill Moran Potential Technologies http://www.potentialtech.com
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
