Just checked my indices, looks like the only table in all my 5 DB's
that has a hash index is the one I ran tests on. Should play in the
lottery this week :-) Will check this tomorrow and recreate the
affected hash indices. As the history of this db is quite long, I
really just can't remember how this happened. Thanks for pointing out
this.
But the other thing is the huge amount of transfer volume when
rsyncing the data dir from the new primary to set up the new
replication slaves. But this should go away when I stop using REINDEX
DATABASE, right ?
Gerhard
On Mon, Apr 11, 2011 at 7:55 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:
>
>> I have e.g. a table with:
>
>> Indexes:
>> "idx_auftrag_l1" hash (a_nr)
>
> Any *hash* index will need to be rebuilt. Like that one.
>
>> Seeing this and reading the docs about the caveats I see no other
>> solution as to REINDEX all of my DB's :-(
>
> As the docs say, you need to REINDEX your *hash* indexes. Most
> people don't use those because they aren't WAL-logged, which can
> lead to various problems (including this one) and because there seem
> to be very few circumstances were they perform better than btree
> indexes, which don't have such a problem.
>
> If you switched to btree indexes where you currently have hash
> indexes, the REINDEX would be totally unneeded.
>
>> I have lot's of tables with primary key a_nr as above (though I
>> know it is not reflected in the table definition, but should
>> result in the same result, i.e. a_nr integer PRIMARY KEY would
>> create an index to lookup the table).
>
> I think a PRIMARY KEY constraint normally builds a btree index,
> which wouldn't have this problem.
>
> -Kevin
>