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/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
