On Mon, 2005-07-18 at 16:01, Dan Armbrust wrote:
> 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%'
OK, how selective are each of these three fields? If codingSchemeName
isn't very selective, then you'll likely never win with an index. Put
the most selective item first, that way the database will have the
smallest set of data to have to play with to get the answer you asked
for.
Next, hash indexes might have been the answer, when I didn't know you
were using like. Hash indexes, at least on my 7.4 box, can't use like.
Ilike can't use indexes period, and it's better to create a functional
index:
create index indexname on table (lower(fieldname));
and then always search on lower(fieldname);
> 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.
The reason your three column index likely doesn't work well in
postgresql is that you probably don't have much selectivity in the first
column. I'm just guessing there aren't that many coding schemes, but
there probably are plenty of properties. So, setting up your index with
that value first, and in first in your where clause should help,
assuming it's more selective.
IF you build a testcase on propertvalue, and try ilike, you'll notice
that it, quite simply does NOT use indexes.