Re: index row size exceeds btree maximum, 2713 - Solutions?

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/