Re: capturing/viewing sort_mem utilization on a per query basis

On Wed, 02 Feb 2005 12:13:59 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> > Although, looking in the log postgresql is generating, I'm seeing the
> > following at the same time as that OOM above so it certainly looks
> > like the DB is barfing:
>
> OK, then it's a backend issue not a client-side issue.
>
> > HashBatchContext: -1934683632 total in 295 blocks; 135760 free (879
> > chunks); -1934819392 used
>
> Hmm, looks like a hash join ran out of memory. What PG version is this
> again, and what do you have sort_mem set to? Can you show an EXPLAIN
> for the query that is failing like this?

This is PostgreSQL-7.4.6 running on RHES-3 (using the RPMs).
sort_mem=100000

I've attached the explain for the query that is blowing up. Thanks
for looking at this.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand http://netllama.linux-sxs.org