Forum OpenACS Development: Re: Performance problem in PG 8.1.2

Posted by Gustaf Neumann on
To add to Tom's statement: The data types are quite important. In case you have numbers to index on, don't use "numeric" as data type, unless you really have to. We found out in one of our applications that a search over integer is much more efficient.

Be as well aware, the postgresql supports different types of indices. I have seen a couple of benchmarks in german it-newspapers comparing mysql and postgresql showing that for large data-sets postgres actually is superior.

Posted by Tom Jackson on
Another point about using tsearch: you should be able to make a copy of the table, add the tsearch column and create the index anywhere. Since the index is part of the row data, this is possible and easy without some hack involved. Then you could copy the data back into the original table, modified. You may have to go offline for a brief time, but you should be able to do all this testing without interfering with the live site.