[postgis-users] Out Of Memory Error on GiST Index

[Joe Healy]

James Cradock wrote:
> Hello.
>
> I'm getting an Out Of Memory error when I try to create a GiST index 
> on a geometry column (PostGIS) with approximately 33,000,000 rows. I 
> can truncate the data in the table, create the GiST index on the empty 
> table, but when I try to reload the data via pg_restore I get the 
> error. I get the error also if I try creating the GiST index on the 
> table with the 33,000,000 rows.
>
> Are there limitations with the GiST index? Has anyone seen this?
I have seen it in a few cases, with both smaller and similar sized 
datasets on both linux and windows. Things that have resulted in this 
have been inserting many rows at once with an index (maybe without 
(can't remember)), creating an index and doing an update to every row in 
a similar sized table.

Our solution has been to do the work on a bigger machine which seems (so 
far) to have reduced the problem. We will find out tomorrow if this will 
fix it for the main operations we need to carry out.

(Basically, we have no idea but are in a similar boat)


Joe