Hmm, well, I don't know if it is actually building an index properly on this column, I just assumed that it was.  It doesn't fail on every insert, only on the one that has a really long text value.  I know it doesn't use the index when I do "ILIKE" queries, resulting in poor performance...  but I assumed that was because I was trying to do a case insensitve search on a case sensitive column index.  I didn't want to go down the road of writing even more database implementation specific code.  I will usually be using Lucene for the full text searches anyway. 

  I haven't seen it mentioned anywhere except a couple of mailing list postings.

All of my other limitations on changing things aside - given a query like this:

Select * from conceptproperty where codingSchemeName='foo' AND property='anotherfoo' and propertyValue ILIKE 'valu%'

What indexe(s) would be recommended? 

My current 3 column index (that works on other DB's) doesn't perform well due to case sensitivity issues, and now fails, due to data length issues.

Dan



Dan

Jaime Casanova wrote:

The index that is failing is CREATE INDEX i1 ON conceptproperty USING btree
(codingschemename, property, propertyvalue).

    

I don't think you could create indexes on text fields... there are
other type of indexes for that... tsearch2 for example