Re: index item size 4496 exceeds maximum 2713

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?

If you answer, would you please send the response to my email address as
well as to this list? Please?

Thanks,

Jon Hassen
jhassen(at)azstarnet(dot)com

Jon,

> When I try and create an index on a text field, I get this
> response:
>
> "ERROR: btree: index item size 4496 exceeds maximum 2713"

What version of Postgres are you running?

-Josh

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?

On 6 Mar 2002, Andrew McMillan wrote:
> On Tue, 2002-03-05 at 13:27, Jon Hassen wrote:

> > "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.
[ ... ]

Interesting note, not that I have the problem right now, but in
another (nameless) dbase, I will also have long fields I want
to index (GIS metadata). Where does this number of 3 come from?
Just some small random integer? Is there some fraction/performance
curve somewhere, which shows 3 is in some sense optimal?

Gord

Matter Realisations http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
780/481-8019 ghaverla @ freenet.edmonton.ab.ca
780/993-1274 (cell)

ghaverla(at)freenet(dot)edmonton(dot)ab(dot)ca writes:
> Where does this number of 3 come from?

The btree code breaks if it's not able to put at least three index
entries on a page.

regards, tom lane

Hello all,

Thank you all very much for the input. I appreciate it. It turns out that
indeed, the biggest thing that can be indexed using btree is some division
(by 3 or 4, it seems to differ) of the size of the tuple size. (Thanks Tom
Lane and Andrew McMillan for pointing me in that direction)

I ended up changing the backend to a 32768 size block, (by using
BLCKSZ=32678 in the pg_configure.h.in file, then running:
configure/gmake/gmake install) which gave me the 8192 (32,768/4) that I had
been looking for.

Be careful if you do this! Make a pg_dump of your current database first!

For those interested, (and since how to do this doesn't seem to exist
anywhere, or it is VERY hard to find anyway) here is how I did it:

Be careful. please.

1. pd_dumpall > outputfile / This makes a backup of your current db.
2. pg_ctl stop -D /usr/local/pgsql/data /stop the pg process
3. Change the file /usr/local/src/postgresql-X.X/src/include/configure.h.in
to reflect the new blocksize that you desire. BLCKSZ = 32768 The 32k number
is the max that you can use, and I would stick with either 8192 or 16384 or
32768.
4. Re-run the configure, then the gmake, then the gmake install.
5. Move the /usr/local/pgsql/data directory or rename it. Try:
mv /usr/local/pgsql/data /usr/local/pgsql/data.old or something like that.
When this is over, you can delete this, but in case you need to go back,
hang on to it. It is your old database(s).
6. Make a new data dir.
(root)# mkdir /usr/local/pgsql/data
(root)# chown postgres /usr/local/pgsql/data
(root)# initlocation -D /usr/local/pgsql/data
(root)# initdb -D /usr/local/pgsql/data
(root)# su postgres
(postgres)# postmaster -D /usr/local/pgsql/data
(postgres)# /usr/local/pgsql/bin/psql -d template1 -f outputfile
(where outputfile is the one you created in step 1.)

That's it. You now have a 32k block size.

btw, I will be trying to implememnt tsearch on this db too. I want it
blazing fast!! faster! Faster! FASTER!!

Jon Hassen
Systems Manager
Arizona Daily Star