Forum OpenACS Development: Re: Performance problem in PG 8.1.2

Collapse
Posted by Janine Ohmer on
For completeness' sake, I was wrong about the version. I took his word for it when he said 8.1.2 but it's actually 8.2.3. I was hoping that would be an easy fix, but no joy.
Collapse
Posted by Tom Jackson on
One problem with giving any advice is not knowing the type of data or the search type you need to do. It isn't surprising that MySQL would do simple count or even search faster than pg.

If you are doing a unique key search, then count is either 0 or 1. If you are doing a text match, then a unique key index isn't going to help much unless it is 'abc%' type. With the low size of 64chars, tsearch might be a very fast index and even pretty efficient on storage.

Collapse
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.

Collapse
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.