From: | Martin Guether <martin(dot)guether(at)campus(dot)tu-berlin(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | postgres/postgis eats memory |
Date: | 2011-10-21 13:05:11 |
Message-ID: | CALv1R+Q6RFJenvhp-dK5Vu_kJOx2LiJNJE7wL_ybQ8tOen117Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi guys,
on a test maschine from my university i'm running into a weird problem.
the issue (i attached detail info):
The running query eats more and more memory.(seen in htop) It'll start
using virtual memory and after eating up that one, too, postgres will
crash with:
psql:/home/mguether/sql/benchmark.sql:6: NOTICE: std::bad_alloc
psql:/home/mguether/sql/benchmark.sql:6: ERROR: GEOS intersects()
threw an error!
once it gave me an
psql:/home/mguether/sql/benchmark.sql:6: ERROR: out of memory
DETAIL: Failed on request of size 182949.
after the crash, all memory/swap is free again, so it really is postgres :)
i don't really know how to debug it any more detailed or fix this
issue in any way.
Does anyone here have a hint, where the issue might be?
thanks,
martin
here some information:
the maschine:
16 GB RAM, i7 4cores x64 2.6.32-21-server ubuntu
installed postgres:
psql (PostgreSQL) 8.4.9
including postgis
POSTGIS="1.4.0" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.7.1, 23 September
2009" USE_STATS
i changed two parameters from the default config:
shared_buffers = 512MB
work_mem = 256MB
using osm2pgsql I important some GEO data, and want to execute this query:
SELECT a.osm_id, w.osm_id FROM "planet_osm_line" w,
"planet_osm_polygon" a WHERE ST_Intersects(w.way,a.way) AND w.highway
IS NOT NULL;
the EXPLAIN gives me:
Nested Loop (cost=0.00..26427424.16 rows=1511021 width=8)
Join Filter: _st_intersects(w.way, a.way)
-> Seq Scan on planet_osm_line w (cost=0.00..49213.10
rows=1137488 width=837)
Filter: (highway IS NOT NULL)
-> Index Scan using osm_areas_index on planet_osm_polygon a
(cost=0.00..23.10 rows=6 width=824)
Index Cond: (w.way && a.way)
(6 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2011-10-21 13:48:19 | Re: PostGIS in a commercial project |
Previous Message | Henry Drexler | 2011-10-21 10:45:47 | Re: plpgsql at what point does the knowledge of the query come in? |