Re: index item size 4496 exceeds maximum 2713

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?