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: (view raw or whole 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

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

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).

Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB:        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 yet?

In response to


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-2015 The PostgreSQL Global Development Group