pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Guesses on what this NestLoop is for?
Date: Mon, 27 Oct 2003 15:32:41 -0800
Message-ID: <200310271532.41974.josh@agliodbs.com> (raw)

Folks,

I'm getting this plan on 7.2.4:

----------------------------------------------------------
explain
select events.event_id, events.event_name, type_name,
	COALESCE(cases.case_name || '(' || cases.docket || ')', 
trial_groups.tgroup_name) as event_case,
	jw_date_format(events.event_date, events.event_tz, events.duration) as 
show_date
FROM event_types, events
	LEFT OUTER JOIN cases ON (events.link_type = 'case' AND events.case_id = 
cases.case_id)
	LEFT OUTER JOIN trial_groups ON ( events.link_type = 'tg' AND
		events.case_id = trial_groups.tgroup_id )
	LEFT OUTER JOIN event_history eh ON events.event_id = eh.event_id
WHERE events.status = 1 or events.status = 11
	and events.event_date > '2003-10-27'
	and events.etype_id = event_types.etype_id
	and (
		( events.mod_user = 562 AND eh.event_id IS NULL )
		OR
		( eh.mod_user = 562
		  and not exists (select 1 from event_history eh2
		  	where eh2.event_id = eh.event_id
			and eh2.mod_date < eh.mod_date) )
	    );

Nested Loop  (cost=100004949.08..2676373923.96 rows=3666858 width=197)
  ->  Hash Join  (cost=4949.08..8519.60 rows=43568 width=165)
        ->  Hash Join  (cost=4407.81..6615.02 rows=43568 width=149)
              ->  Hash Join  (cost=4403.21..6485.29 rows=43568 width=125)
                    ->  Seq Scan on events  (cost=0.00..1515.70 rows=43568 
width=79)
                    ->  Hash  (cost=3108.07..3108.07 rows=115355 width=46)
                          ->  Seq Scan on cases  (cost=0.00..3108.07 
rows=115355 width=46)
              ->  Hash  (cost=4.43..4.43 rows=143 width=24)
                    ->  Seq Scan on trial_groups  (cost=0.00..4.43 rows=143 
width=24)
        ->  Hash  (cost=524.72..524.72 rows=13240 width=16)
              ->  Seq Scan on event_history eh  (cost=0.00..524.72 rows=13240 
width=16)
  ->  Seq Scan on event_types  (cost=0.00..4.32 rows=106 width=32)
  SubPlan
    ->  Seq Scan on event_history eh2  (cost=0.00..557.82 rows=1 width=0)
-----------------------------------------------------------------

What I can't figure out is what is that inredibly expensive nested loop for?   
If I could figure that out, maybe I could query around it.

Unfortunately, I can't EXPLAIN ANALYZE because the present query swamps the 
machine, and it's a production server.  Also it never completes.

And yes, the system is vacuum full analyzed.   Event_history is under-indexed, 
but the other tables are heavily indexed.

Ideas?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




view thread (3+ messages)  latest in thread

Message-ID: <200310271532.41974.josh@agliodbs.com>
Permalink:  ../200310271532.41974.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200310271532.41974.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com
  Subject: Re: Guesses on what this NestLoop is for?
  In-Reply-To: <200310271532.41974.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox