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?