[postgis-users] GiST index and null values

[Schmid Andreas]

Hi,

I stumbled upon a table in our PostGIS 3.1 database as follows:
Size of table: 40MB
Size of GiST index on a point geometry column: 40MB
Size of GiST index on a linestring geometry column: 980MB!

The table contains 140'000 rows. Only 4'000 of them do have a value in the linestring geometry column. (If there is a value set, it's a simple linestring consisting of 2 points.)

I suppose it's those null values which cause the huge index size. What could be the explanation for this behaviour?

Should I reduce the index size by adding a "WHERE column IS NOT NULL" to the index? Or is it even better to drop the index completely, given the table contains only 4'000 simple geometries in this column? (The geometries are mainly used for rendering in a WMS.)


Thanks a lot,
Andy