Thread

1. index item size 4496 exceeds maximum 2713

   Jon Hassen <jhassen@azstarnet.com> — 2002-03-05T00:27:59Z

   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@azstarnet.com
   
   
   

2. Re: index item size 4496 exceeds maximum 2713

   Josh Berkus <josh@agliodbs.com> — 2002-03-05T00:30:34Z

   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
   
   

3. Re: index item size 4496 exceeds maximum 2713

   Andrew McMillan <andrew@catalyst.net.nz> — 2002-03-06T06:55:42Z

   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?
   
   
   

4. Re: index item size 4496 exceeds maximum 2713

   ghaverla@freenet.edmonton.ab.ca — 2002-03-06T10:13:19Z

   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)
   
   
   
   

5. Re: index item size 4496 exceeds maximum 2713

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-03-06T16:14:23Z

   ghaverla@freenet.edmonton.ab.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
   
   

6. Re: index item size 4496 exceeds maximum 2713

   Jon Hassen <jhassen@azstarnet.com> — 2002-03-07T19:37:37Z

   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