Can you do? alter table placex add column geometry_sector integer; update placex set geometry_sector = geometry_sector(geometry);
P. On Wed, Mar 24, 2010 at 1:15 PM, Frans Hals <fha...@googlemail.com> wrote: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general