Re: [PERFORM] Guesses on what this NestLoop is for?

[Manfred Koizar]

On Mon, 27 Oct 2003 15:32:41 -0800, Josh Berkus [EMAIL PROTECTED]
wrote:
FROM event_types, events
   LEFT OUTER JOIN ...
WHERE events.status = 1 or events.status = 11
   and events.event_date  '2003-10-27'
   and events.etype_id = event_types.etype_id
   and ( ...
   );


What I can't figure out is what is that inredibly expensive nested loop for?   

Sorry, I have no answer to your question, but may I ask whether you
really want to get presumably 106 output rows for each event with
status 1?

Or did you mean
 WHERE (events.status = 1 OR events.status = 11) AND ...

Ideas?

I'd also try to push that NOT EXISTS condition into the FROM clause:

...LEFT JOIN (SELECT DISTINCT ON (event_id)
 event_id, mod_date, mod_user
FROM event_history
   ORDER BY event_id, mod_date
 ) AS eh ON (events.event_id = eh.event_id) ...
WHERE ...
  AND CASE WHEN eh.event_id IS NULL
   THEN events.mod_user
   ELSE eh.mod_user END = 562

If mod_user is NOT NULL in event_history, then CASE ... END can be
simplified to COALESCE(eh.mod_user, events.mod_user).

Servus
 Manfred

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[PERFORM] Guesses on what this NestLoop is for?

[Josh Berkus]

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=14949.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


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly