2

I have a huge table with a primary key and a btree_gist index. After some years of operation, the performance of selections went constantly down. I assume the gist index is fragmented.

When reindexing, I get the following error message (after about 10 hours, so it's not easy to reproduce this):

ERROR:  index row size 80 exceeds maximum 8152 for index "idx_test1_id1_id2_validtime_systime"

The table/index looks like this:

create table test1 (
   id1 bigint not null,
   id2 bigint not null,
   validtime tstzrange not null,
   systime tstzrange not null,
   data float);
CREATE INDEX idx_test1_id1_id2_validtime_systime ON test1
USING gist
(
   id1,
   id2,
   validtime,
   systime
)

There are two things which confuse me:

  1. one row of the gist index should be constant 66 bytes
  2. 80 does not exceed 8152

There was no problem creating the index in the first place. The index is still intact and is used by the optimizer. Still reindexing fails. Any hints what could be the problem?

Edit: adding relevant version information as requested in comments:

select version();
PostgreSQL 12.19 (Ubuntu 12.19-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

select extversion from pg_extension where extname='btree_gist';
1.5
2
  • 1
    Are you using anything GiST-specific, or did you pick btree_gist over plain btree as default? Can you share the output of select version(),extversion from pg_extension where extname='btree_gist';? Did you try to create a new, separate index alongside this one, using that same definition, and try to reindex that to see if it breaks the same way? Commented Jul 8, 2024 at 15:26
  • 1
    What is the full version? select version(). Commented Jul 8, 2024 at 16:44

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.