Home > PostgreSQL > Row size and Btree index

Row size and Btree index

At work today saw an error while inserting data into a table:

ERROR: index row size 2962 exceeds btree maximum, 2713

Looking at the HINT in the error, found out that you cant have the row size of more then 1/3th of the PostgreSQL block size (8K by default) for a btree index on the column.

The best is to start using PostgreSQL full text search option for these kind of scenarios as it will be much faster and easier… but in my case that was not an option so had to somehow get around this problem without using text search engine.

After a little bit of research two possible solutions I could find out were (where column was of text data type):

–  Create a hash index on the same column as it will let you store more then 1/3rd of the block size, but a couple of things you need to consider before you go that route…

  • It lets you get the data inserted for more then the 1/3rd of the block size but that again has limitation, you cant go above the PG block size and even closer to that.
  • With Hash indexing you wont be able to use the text pattern matching functions “LIKE” and “ILIKE”, you are left with “=”

–  The other solution is to use the partial index where CREATE INDEX  will look something like this…

        create index .... where length(clm_name) < 500

…this will require changing the queries as well, now you will have to add “where length(clm_name) < 500” while doing SELECTS.


Shoaib Mir
shoaibmir[@]gmail.com


					
Advertisements
  1. September 23, 2009 at 10:57 pm

    Are you sure that you really want that index there?

    You see, Indexes only work for front-anchored comparisons.

    So I assume that the column in question is a text column.

    The only way a non-fulltext-index can be used is for queries like

    clm_name = ‘term’

    or

    clm_name LIKE ‘term%’

    all other variations (‘%term%’ or ‘term%’) will not be able to use your index and will cause a table scan (unless you have other limiting factors).

    To only way around this limitation is to bite the bullet and hook up some kind of full-text indexing.

    • September 24, 2009 at 12:07 am

      Yes, the column in question is a text one as I mentioned in the post as well. Well most of the operations are mainly with ‘=’ and using LIKE operator which is why its good for now… but you are right with the full-text option as I mentioned in the post as well, is something we do have as a long term plan to implement for such scenarios.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: