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:
- one row of the gist index should be constant 66 bytes
- 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
btree_gistover plain btree as default? Can you share the output ofselect 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?select version().