Thread: 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@azstarnet.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@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
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