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.
>
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
And a devx article here:
http://www.devx.com/opensource/Article/21674/0
> 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?
Why don't you do this:
Select * from conceptproperty where codingSchemeName='foo' AND
property='anotherfoo' and propertyValue ~ lower('valu');
and have an index:
create index lower_propertyvalue_idx on
conceptproperty(lower(propertyvalue));
and have a index:
(codingschemename,property)
I would also be curious to see an explain analyze.
Sincerely,
Joshua D. Drake
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/