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

From: Dan Armbrust <daniel(dot)armbrust(dot)list(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: index row size exceeds btree maximum, 2713 - Solutions?
Date: 2005-07-18 21:01:06
Message-ID: 42DC1892.1040405@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hmm, well, I don't know if it is actually building an index properly on
this column, I just assumed that it was.&nbsp; It doesn't fail on every
insert, only on the one that has a really long text value.&nbsp; I know it
doesn't use the index when I do "ILIKE" queries, resulting in poor
performance...&nbsp; but I assumed that was because I was trying to do a
case insensitve search on a case sensitive column index.&nbsp; I didn't want
to go down the road of writing even more database implementation
specific code.&nbsp; I will usually be using Lucene for the full text
searches anyway.&nbsp; <br>
<br>
Where is the documentation on tsearch2?&nbsp; I haven't seen it mentioned
anywhere except a couple of mailing list postings.<br>
<br>
All of my other limitations on changing things aside - given a query
like this:<br>
<br>
Select * from conceptproperty where codingSchemeName='foo' AND
property='anotherfoo' and propertyValue ILIKE 'valu%'<br>
<br>
What indexe(s) would be recommended?&nbsp; <br>
<br>
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.<br>
<br>
Dan<br>
<br>
<br>
<br>
Dan<br>
<br>
Jaime Casanova wrote:
<blockquote cite="midc2d9e70e05071813323a499a41(at)mail(dot)gmail(dot)com"
type="cite">
<blockquote type="cite">
<pre wrap="">The index that is failing is CREATE INDEX i1 ON conceptproperty USING btree
(codingschemename, property, propertyvalue).

</pre>
</blockquote>
<pre wrap=""><!---->
I don't think you could create indexes on text fields... there are
other type of indexes for that... tsearch2 for example

</pre>
</blockquote>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 1.8 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Juan Miguel Paredes 2005-07-18 21:03:53 Composite type within a composite type?
Previous Message Scott Marlowe 2005-07-18 20:55:46 Re: index row size exceeds btree maximum, 2713 -