From: | Anthony <osm(at)inbox(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | memory usage of group by select |
Date: | 2009-12-29 20:41:21 |
Message-ID: | 71cd4dd90912291241p57d8ef98w9fd4feda573eb677@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
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'
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-12-29 20:43:06 | Re: Planner Row Estimate with Function |
Previous Message | Leonardo M. Ramé | 2009-12-29 20:24:43 | Re: DDL commands take forever |