Large index operation crashes postgres

From: Frans Hals <fhals7(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Large index operation crashes postgres
Date: 2010-03-24 20:15:56
Message-ID: 39af1ed21003241315g92528b4u97603d6bb263d61d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-03-24 20:26:52 Re: Large index operation crashes postgres
Previous Message Ovid 2010-03-24 18:54:39 Re: Fw: Clipping values