PostgreSQL: Issue with a corrupted GiST index on ll_to_earth

corruptiongist-indexpostgresql

I'm facing something that seems like an index corruption issue on a table that stores about 120k entries.
Each entry has a latlon and this latlon is indexed using the ll_to_earth GiST implementation:

CREATE INDEX table_geo ON table USING gist(ll_to_earth(lat, lon));

Now if I query the latlon of a specific entry located at -5.182, -80.622 using earth_box like this:

SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon);

I don't have any result, although the same request using the primary key always returns the expected entry:

SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon)
  AND id = 999;

> 1 result:
  id     lat     lon
  999    34.01   -117.94

Interestingly, if I increase the radius of the earth_box by a lot, I'm able to see the entry again! I can even find the new location of the entry in the btree by increasing/decreasing the latlon and the radius, but if I reduce the radius too much, I'll lose it because of the Recheck.

I found of that out of 123k rows, 14k (11%) are corrupted.

Query flows

Without the primary key:

EXPLAIN SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon);

Bitmap Heap Scan on table  (cost=9.23..495.43 rows=123 width=16)
    Recheck Cond: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)
      ->  Bitmap Index Scan on table_geo  (cost=0.00..9.20 rows=123 width=0)
            Index Cond: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)

And with the primary key:

EXPLAIN SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon)
  AND id = 999;

Index Scan using table_pk on table  (cost=0.42..8.69 rows=1 width=16)
    Index Cond: (id = 999)
    Filter: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)

The second query is a simple Index Scan which I think is guaranteed to find the row.

The first query however uses a Bitmap Index Scan on the index + a Bitmap Heap Scan so I can imagine 2 scenarios that would make it fail:

  • the entry is located in the wrong btree node (= Heap Scan fails)
  • btree nodes have wrong dimensions (= Index Scan fails)

Things I tried

UPDATE table SET lat = lat + 0.000001 WHERE id = 999;

This is always enough to fix 1 or several specific entries. So I thought I would fix everything by running this query on the whole table but it did not. Some rows were fixed but some others got broken.
The overall corruption rate was still around 10-12%.

Then I read about REINDEX on the postgres manual, so I tried that:

REINDEX INDEX table_geo;

But the same thing happened, some entries got fixed and some others stopped working.

My postgres version on prod:

PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

Extensions used:

  • cube (1.0)
  • earthdistance (1.0)

Best Answer

Looks like a bug in either the GiST index implementation, the ll_to_earth() function (or maybe the specific operator / operator class). You have pretty much ruled out plain index corruption by trying REINDEX.

First try to upgrade to a current version of Postgres and see if this fixes your problem. 9.5 is getting old (and the current point release is 9.5.15). I did not find any specific entries concerning your case in the release notes since 9.5.10 - on a quick glance! At least upgrade to latest point release, currently 9.5.15 - as is the recommendation of the project. Or to the latest version Postgres 11.1 if that's an option, and see if the problem goes away. (Or recreate your db in pg 11 just to test.)

Both extensions cube and earthdistance have been updated, too. Check curent versions for your installation with:

SELECT * FROM pg_available_extensions where name IN ('cube', 'earthdistance');

For Postgres 11:

cube    1.4  
earthdistance   1.1

To see current versions for any given Postgres version try this fiddle.

If upgrading does not fix the problem please file a bug report.