pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGuesses on what this NestLoop is for?
3+ messages / 2 participants
[nested] [flat]
* Guesses on what this NestLoop is for?
@ 2003-10-27 23:32 Josh Berkus <josh@agliodbs.com>
2003-10-28 10:59 ` Re: Guesses on what this NestLoop is for? Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2003-10-27 23:32 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Guesses on what this NestLoop is for?
2003-10-27 23:32 Guesses on what this NestLoop is for? Josh Berkus <josh@agliodbs.com>
@ 2003-10-28 10:59 ` Manfred Koizar <mkoi-pg@aon.at>
2003-10-28 17:36 ` Re: Guesses on what this NestLoop is for? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Manfred Koizar @ 2003-10-28 10:59 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
On Mon, 27 Oct 2003 15:32:41 -0800, Josh Berkus <josh@agliodbs.com>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Guesses on what this NestLoop is for?
2003-10-27 23:32 Guesses on what this NestLoop is for? Josh Berkus <josh@agliodbs.com>
2003-10-28 10:59 ` Re: Guesses on what this NestLoop is for? Manfred Koizar <mkoi-pg@aon.at>
@ 2003-10-28 17:36 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2003-10-28 17:36 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: pgsql-performance
Manfred,
> 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 ...
Thanks! I spent so much time tinkering around with the exists clauses, I
completely missed that. Hopefully I'll get this client to upgrade to 7.4 so
that the explains will be more readable ....
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-10-28 17:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-27 23:32 Guesses on what this NestLoop is for? Josh Berkus <josh@agliodbs.com>
2003-10-28 10:59 ` Manfred Koizar <mkoi-pg@aon.at>
2003-10-28 17:36 ` Josh Berkus <josh@agliodbs.com>
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