Re: Trigram performance penalty on varchar?

On Feb 21, 2008, at 10:56 AM, Mario Lopez wrote:

> Hi,
>
> I am indexing a 100 million record table composed of varchar(255)
> as the field to be indexed. I have always seen that examples of
> pg_trgm are based on text type fields. Is this by any special reason?.

A varchar is internally represented as text, with a size constraint
of 255 characters in your case (I'm assuming your data requires that
constraint?).

> My computer is creating the index since 5 hours ago so I guess
> there must be something wrong...

I guess your server is running low on memory and the index being
created doesn't fit in memory. You may want to have a look at http://
www.postgresql.org/docs/8.3/interactive/populate.html#POPULATE-WORK-MEM

Another reason may be an exclusive lock on a row that you're trying
to index, but that would mean that some transaction on some client
somewhere is keeping that lock for a very long time (should not
happen). You can check the pg_locks and pg_stat_activity tables for
that.

Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,47c159af233092392031086!