Skip site navigation (1) Skip section navigation (2)

Re: index item size 4496 exceeds maximum 2713

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Jon Hassen <jhassen(at)azstarnet(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: index item size 4496 exceeds maximum 2713
Date: 2002-03-06 06:55:42
Message-ID: 1015397743.994.3482.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, 2002-03-05 at 13:27, Jon Hassen wrote:
> Hello,
> 
>   When I try and create an index on a text field, I get this response:
> 
> "ERROR:  btree: index item size 4496 exceeds maximum 2713"
> 
>   On another PGSQL database I only got that message when the size was above
> 8192. How can I change my database to use the full 8192 size? Or how can I
> get around this problem at all?

The maximum _indexable_ field size is 1/3 of the blocksize.  In most
cases this will be 2713 which is 8192/3.

In reality there is usually very little value in indexing fields larger
than a few hundred bytes, unless you are doing keyword or full-text
indexing.

For indexing like that, you need to look into contrib/tsearch or
suchlike.

If you really do want to index the field, you may want to index on a
substring of the field:

First code up a function as follows:

CREATE FUNCTION myfunc ( text ) RETURNS text AS ' SELECT substr( $1, 1,
50 ); ' LANGUAGE 'sql';

Now, you have to mark the function as 'cachable' - meaning that for the
same input, it will return the same output:

UPDATE pg_proc SET proiscachable = TRUE WHERE proname = 'myfunc';

Finally, create an index on it:

CREATE INDEX myindex ON mytable( myfunc( myfield ) );


This will just be indexing on the first 50 characters of your string -
which for indexing purposes is probably about five times more than you
need, unless you need uniqueness.  If you need uniqueness you will just
have to say 'only x characters are unique', where x < 2713, and then do
the above.

As a subtle enhancement, the function might be quicker in PL/PGSQL (not
sure about that since there is no actual database hit in that SQL).

Regards,
						Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


In response to

Responses

pgsql-novice by date

Next:From: Faudzy SulaimanDate: 2002-03-06 07:00:36
Subject: Postmaster with -i
Previous:From: Andrew McMillanDate: 2002-03-05 21:57:43
Subject: Re: LIKE with no wildcards problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group