I found that by replacing the postgresql.conf file with the original that is present following an initdb the query ran without a memory problem. I looked at the "bad" configuration file and couldn't see anything wrong with it. I regret that because of a typing error the bad file was accidentally deleted. I have subsequently been unable to reproduce the bad behavior. After editing the original file to be the same as what I had intended for the erased file the query still ran without a problem. Memory usage topped out at about 2.1 GB. Even setting work_mem and maintenance_work_mem to 30000MB did not change the maximum memory usage during the query.
Regards, Rae Stiening On Mar 31, 2013, at 1:16 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > stien...@comcast.net writes: >> The query: >> SELECT pts_key,count(*) >> FROM tm_tm_pairs GROUP BY pts_key HAVING count(*) !=1 ORDER BY >> pts_key > >> Which is executed as: >> GroupAggregate (cost=108680937.80..119278286.60 rows=470993280 width=4) >> Filter: (count(*) <> 1) >> -> Sort (cost=108680937.80..109858421.00 rows=470993280 width=4) >> Sort Key: pts_key >> -> Seq Scan on tm_tm_pairs (cost=0.00..8634876.80 rows=470993280 >> width=4) > >> uses all available memory (32GB). pts_key is an integer and the table >> contains about 500 million rows. > > That query plan doesn't look like it should produce any undue memory > consumption on the server side. How many distinct values of pts_key are > there, and what are you using to collect the query result client-side? > psql, for instance, would try to absorb the whole query result > in-memory, so there'd be a lot of memory consumed by psql if there are > a lot of pts_key values. (You can set FETCH_COUNT to alleviate that.) > > A different line of thought is that you might have set work_mem to > an unreasonably large value --- the sort step will happily try to > consume work_mem worth of memory. > > regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
