Re: index row requires 10040 bytes, maximum size is 8191

On 13/11/2010 4:52 AM, Joshua D. Drake wrote:
> On Fri, 2010-11-12 at 15:47 -0500, akp geek wrote:
>> Hi all -
>>
>> I am trying to create an index on character varying field.
>> The
>> column is just character varying with no limit. I am getting the
>> following
>> error " index row requires 10040 bytes, maximum size is 8191"
>> What can I do the postgres.conf to handle this error? Appreciate your
>> help
>
> You can't. You could create a index on "part" of the data or use full
> text.

Ouch, really?

I'd always assumed that btree indexes of big TOASTed values would do a
prefix match check then recheck against the heap if there's a match.
More fool me for making such an assumption.

This doesn't seem like a problem when dealing with fields that're meant
to contain big blobs of text, but it's a strong contraindication for the
advice usually given on this list to avoid varchar(n) in favour of
"text". If a "text" field >8kb in an indexed column will be rejected
because it cannot be indexed, that's a reason to set an explicit limit.
Additionally, not having such constraints would make it much harder to
*add* indexes to "text" columns not already indexed.

craig=> create table test ( x text );
craig=> create index test_x on test(x);
craig=> insert into test(x) values ( repeat('x', 9000) );
INSERT 0 1
craig=> insert into test(x) values ( repeat('x', 90000) );
INSERT 0 1
craig=> insert into test(x) values ( repeat('x', 900000) );
ERROR: index row requires 10324 bytes, maximum size is 8191

It seems like an index method that's smart enough to do prefix-and-hash
comparision, then a heap recheck, would be ideal. It's not really a
common enough issue to be a TODO item, though, as this is the first time
I've seen it come up on the list.

Thoughts, folks? Does this matter in practice, since anything you'd want
to index will in practice be small enough or a candidate for full-text
indexing?

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/