Forum OpenACS Development: Re: Performance problem in PG 8.1.2

Collapse
6: Re: Performance problem in PG 8.1.2 (response to 3)
Posted by Janine Ohmer on 05/16/07 11:45 PM
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
7: Re: Performance problem in PG 8.1.2 (response to 6)
Posted by Tom Jackson on 05/17/07 01:18 AM
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
8: Re: Performance problem in PG 8.1.2 (response to 7)
Posted by Gustaf Neumann on 05/17/07 12:18 PM
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
9: Re: Performance problem in PG 8.1.2 (response to 8)
Posted by Tom Jackson on 05/17/07 04:43 PM
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.