Hi,
running a geo-database from a dump restore where still one of the most
important indexes is missing and so the search is slow.
Whenever I try to add the follwing index to the table "placex", one of
the postmaster processes dies and the server restarts.
I try:
CREATE INDEX idx_placex_sector ON placex USING btree
(geometry_sector(geometry), rank_address, osm_type, osm_id);
The table counts around 50.000.000 rows.
The first 20.000.000 are indexed in 20-30 minutes. Nice!
Then indexing becomes slow and slower, first taking 100.000 rows in
ten minutes while further consequently decreasing speed.
When the job reaches something around row 25.000.000 postgres goes down:
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
I have checked RAM and changed the HD with no success.
Experimenting with a lot of different memory settings in the conf-file
didn't help either.
Is there anybody else who experienced this and found a way to create this index?
Server is postgres 8.3.9 with 4 GB dedicated RAM.
gemoetry_sector function looks like this (postgis):
DECLARE
NEWgeometry geometry;
BEGIN
-- RAISE WARNING '%',place;
NEWgeometry := place;
IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
OR ST_Y(ST_Centroid(NEWgeometry))::text in
('NaN','Infinity','-Infinity') THEN
NEWgeometry := ST_buffer(NEWgeometry,0);
IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
OR ST_Y(ST_Centroid(NEWgeometry))::text in
('NaN','Infinity','-Infinity') THEN
RETURN NULL;
END IF;
END IF;
RETURN (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 +
(500-ST_Y(ST_Centroid(NEWgeometry))::integer);
END;
The subcalled St_Centroid is a postgis C-function located in
/usr/lib/postgresql/8.3/lib/liblwgeom.
Anybody out there has an idea what happens or better how to reach the
50.000.000?
Thanks
Frans
