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.
Where is the documentation on tsearch2? 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:
<blockquote cite="midc2d9e70e05071813323a499a41@mail.gmail.com"
type="cite">
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