Re: Slow index

On Thu, 25 Sep 2008, Tom Lane wrote:
> Matthew Wakeling <matthew(at)flymine(dot)org> writes:
>> Hi all. I'm having an interesting time performance-wise with a set of indexes.
>> Any clues as to what is going on or tips to fix it would be appreciated.
>
> Are the indexed columns all the same datatype? (And which type is it?)

Gene.key_primaryidentifier is a text column
Gene.key_secondaryidentifier is a text column followed by an integer
OntologyTerm.key_name_ontology is a text column followed by an integer
Protein.key_primaryacc is a text column
Publication.key_pubmed is a text column
Synonym.key_synonym is an integer, two texts, and an integer

In most cases, the first text will be enough to uniquely identify the
relevant row.

> It might be helpful to REINDEX the "slow" index. It's possible that
> what you're seeing is the result of a chance imbalance in the btree,
> which reindexing would fix.

That's unlikely to be the problem. When the application starts, the
database has just been loaded from a dump, so the indexes are completely
fresh. The behaviour starts out bad, and does not get progressively worse.

I don't know - is there likely to be any locking getting in the way? Our
write traffic is fairly large chunks of binary COPY in. Could it be
locking the index while it adds the write traffic to it?

Matthew

--
Most books now say our sun is a star. But it still knows how to change
back into a sun in the daytime.