X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7385CD1B559 for ; Mon, 27 Oct 2003 23:35:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53119-06 for ; Mon, 27 Oct 2003 19:35:22 -0400 (AST) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 5F208D1B544 for ; Mon, 27 Oct 2003 19:35:19 -0400 (AST) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3831915 for pgsql-performance@postgresql.org; Mon, 27 Oct 2003 15:35:46 -0800 Content-Type: text/plain; charset="us-ascii" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: PostgreSQL Performance Subject: Guesses on what this NestLoop is for? Date: Mon, 27 Oct 2003 15:32:41 -0800 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310271532.41974.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/695 X-Sequence-Number: 4443 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 || ')',=20 trial_groups.tgroup_name) as event_case, jw_date_format(events.event_date, events.event_tz, events.duration) as=20 show_date FROM event_types, events LEFT OUTER JOIN cases ON (events.link_type =3D 'case' AND events.case_id = =3D=20 cases.case_id) LEFT OUTER JOIN trial_groups ON ( events.link_type =3D 'tg' AND events.case_id =3D trial_groups.tgroup_id ) LEFT OUTER JOIN event_history eh ON events.event_id =3D eh.event_id WHERE events.status =3D 1 or events.status =3D 11 and events.event_date > '2003-10-27' and events.etype_id =3D event_types.etype_id and ( ( events.mod_user =3D 562 AND eh.event_id IS NULL ) OR ( eh.mod_user =3D 562 and not exists (select 1 from event_history eh2 where eh2.event_id =3D eh.event_id and eh2.mod_date < eh.mod_date) ) ); Nested Loop (cost=3D100004949.08..2676373923.96 rows=3D3666858 width=3D197) -> Hash Join (cost=3D4949.08..8519.60 rows=3D43568 width=3D165) -> Hash Join (cost=3D4407.81..6615.02 rows=3D43568 width=3D149) -> Hash Join (cost=3D4403.21..6485.29 rows=3D43568 width=3D= 125) -> Seq Scan on events (cost=3D0.00..1515.70 rows=3D43= 568=20 width=3D79) -> Hash (cost=3D3108.07..3108.07 rows=3D115355 width= =3D46) -> Seq Scan on cases (cost=3D0.00..3108.07=20 rows=3D115355 width=3D46) -> Hash (cost=3D4.43..4.43 rows=3D143 width=3D24) -> Seq Scan on trial_groups (cost=3D0.00..4.43 rows= =3D143=20 width=3D24) -> Hash (cost=3D524.72..524.72 rows=3D13240 width=3D16) -> Seq Scan on event_history eh (cost=3D0.00..524.72 rows= =3D13240=20 width=3D16) -> Seq Scan on event_types (cost=3D0.00..4.32 rows=3D106 width=3D32) SubPlan -> Seq Scan on event_history eh2 (cost=3D0.00..557.82 rows=3D1 width= =3D0) ----------------------------------------------------------------- What I can't figure out is what is that inredibly expensive nested loop for= ?=20=20=20 If I could figure that out, maybe I could query around it. Unfortunately, I can't EXPLAIN ANALYZE because the present query swamps the= =20 machine, and it's a production server. Also it never completes. And yes, the system is vacuum full analyzed. Event_history is under-index= ed,=20 but the other tables are heavily indexed. Ideas? --=20 -Josh Berkus Aglio Database Solutions San Francisco