I'm running a group by query on a table with over a billion rows and my memory usage is seemingly growing without bounds. Eventually the mem usage exceeds my physical memory and everything starts swapping. Here is what I gather to be the relevant info:
My machine has 768 megs of ram.
shared_buffers = 128MB work_mem = 8MB # this was originally higher, but I brought it down to try to fix the problem - it hasn't maintenance_work_mem = 256MB fsync = off checkpoint_segments = 30 effective_cache_size = 256MB #this was originally 512MB but I just recently brought it down - as I expected that didn't affect anything
data=# explain select pid, min(oid) into nd_min from nd group by pid; QUERY PLAN ------------------------------------------------------------------------ HashAggregate (cost=28173891.00..28174955.26 rows=85141 width=8) -> Seq Scan on nd (cost=0.00..21270355.00 rows=1380707200 width=8) (2 rows)
data=# \d+ nd Table "fullplanet091207osm.nd" Column | Type | Modifiers | Storage | Description --------+---------+-----------+---------+------------- oid | integer | not null | plain | pid | integer | not null | plain | ref | integer | | plain | Indexes: "nd_pkey" PRIMARY KEY, btree (pid, oid) Has OIDs: no
VERSION = 'PostgreSQL 8.4.1 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.1-3ubuntu3) 4.4.1, 64-bit'