From: | Bill Moran <wmoran(at)potentialtech(dot)com> |
---|---|
To: | Tom Wilcox <hungrytom(at)googlemail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Out of Memory and Configuration Problems (Big Computer) |
Date: | 2010-05-28 16:54:49 |
Message-ID: | 20100528125449.4190f7f0.wmoran@potentialtech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In response to Tom Wilcox <hungrytom(at)googlemail(dot)com>:
> In addition, I have discovered that the update query that runs on each row
> of a 27million row table and fails with Out of memory error will work when
> limited to 1million rows in an extremely shorter period of time:
>
> EXPLAIN ANALYZE
> UPDATE nlpg.match_data SET org = normalise(org) WHERE match_data_id
> < 1000000;
>
> "Index Scan using match_data_pkey1 on match_data (cost=0.00..3285969.97
> rows=1147685 width=206) (actual time=0.280..18018.601 rows=999999 loops=1)"
> " Index Cond: (match_data_id < 1000000)"
> "Total runtime: 182732.207 ms"
>
>
> Whereas this fails with Out of Memory:
>
> UPDATE nlpg.match_data SET org = normalise(org) WHERE match_data_id;
You're not liable to get shit for answers if you omit the mailing list from
the conversation, especially since I know almost nothing about tuning
PostgreSQL installed on Windows.
Are there multiple queries having this problem? The original query didn't
have normalise() in it, and I would be highly suspicious that a custom
function may have a memory leak or other memory-intensive side-effects.
What is the code for that function?
For example, does:
UPDATE nlpg.match_data SET org = org WHERE match_data_id;
finish in a reasonable amount of time or exhibit the same out of memory
problem?
It'd be nice to see a \d on that table ... does it have any triggers or
cascading foreign keys?
And stop
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
From | Date | Subject | |
---|---|---|---|
Next Message | Nilesh Govindarajan | 2010-05-28 16:56:58 | Re: No lidbl.so in libpq.so (postgresql 8.4.4) |
Previous Message | Gary Fu | 2010-05-28 16:43:59 | Re: No lidbl.so in libpq.so (postgresql 8.4.4) |