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

Lonni J Friedman <netllama@gmail.com> writes:
> On Wed, 02 Feb 2005 12:13:59 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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?

> I've attached the explain for the query that is blowing up.

One of the three Hash steps must be absorbing a whole lot more rows than
the planner is expecting, but all of them look like fairly
straightforward estimation situations:

               ->  Hash  (cost=108.96..108.96 rows=28 width=24)
                 ->  Index Scan using mntr_subscr_usrevt on mntr_subscription sfmain_monitoringsubscriptio0
(cost=0.00..108.96rows=28 width=24) 
                       Index Cond: (((user_id)::text = 'user1187'::text) AND ((event_operation)::text =
'update'::text))

   ->  Hash  (cost=701.44..701.44 rows=34444 width=24)
         ->  Seq Scan on field_value tracker_artifact_group0  (cost=0.00..701.44 rows=34444 width=24)

             ->  Hash  (cost=5.74..5.74 rows=1 width=80)
                   ->  Index Scan using project_path on project tracker_artifact_extension_f1  (cost=0.00..5.74 rows=1
width=80)
                         Index Cond: (("path")::text = 'projects.meeting_broker_v3'::text)
                         Filter: ((("path")::text = 'projects.meeting_broker_v3'::text) OR (("path")::text ~~
'projects.meeting_broker_v3.%'::text))

Perhaps one of these tables hasn't been vacuumed/analyzed and is way
bigger than the planner thinks?  Can you check into how many rows
actually meet the identified conditions?

            regards, tom lane