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
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 |