Search Postgresql Archives

postgres/postgis eats memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux