X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 98A8F475458 for ; Wed, 9 Apr 2003 20:15:42 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 2D6A7474E42 for ; Wed, 9 Apr 2003 20:15:41 -0400 (EDT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2954009 for pgsql-performance@postgresql.org; Wed, 09 Apr 2003 17:15:24 -0700 Content-Type: text/plain; charset="us-ascii" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Pgsql-Performance Subject: Help analyzing 7.2.4 EXPLAIN Date: Wed, 9 Apr 2003 17:15:19 -0700 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200304091715.19565.josh@agliodbs.com> X-Spam-Status: No, hits=-12.2 required=5.0 tests=BAYES_10,USER_AGENT_KMAIL autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/97 X-Sequence-Number: 1603 Folks, What follows is a 7.2.4 EXPLAIN ANALYZE statement for the shown query. Thi= s=20 query is currently taking 570 msec, an OK amount of time until you realize= =20 that the system only has test data currently, and the tables in full=20 production will have 100-1000 times as much data. Becuase it's 7.2.4, it's a little hard to tell exactly which part of the= =20 query is taking up 90% of the processing time. The line which claims to = be=20 taking that time is: -> Seq Scan on users (cost=3D0.00..3595.33 rows=3D12 width=3D87) (actua= l=20 time=3D13.50..547.59 rows=3D41 loops=3D1) However, since users has only 200 records, I suspect that what's actually= =20 being represented here is the processing time for the PL/pgSQL procedure in= =20 the correlated subselect, if_addendee_conflict(). Questions:=20=20 1. Can anyone confirm my analysis in the paragraph above? 2. Can anyone point out any obvious ways to speed up the query below? 3. In the query below, if_attendee_conflict needs to be run once for each= =20 (attorney times events) on the same day. Further, if_attendee_conflict=20 involves a database lookup in 10-25% of cases. Given that=20 if_attendee_conflict needs to apply complex criteria to determine whether o= r=20 not there is a conflict, can anyone suggest possible ways to cut down on th= e=20 number of required loops? Thanks everyone! Query and analyze follows. j_test=3D> explain analyze SELECT users.user_id, (users.fname || COALESCE(' ' || users.minit, '') || '= '=20 || users.lname) as atty_name, users.lname, (SELECT if_addendee_conflict(users.user_id, 3272, '2003-04-15 10:00', '1= =20 days'::INTERVAL, events.event_id, events.event_date, events.duration, event_cats.status, = '30=20 minutes') as cflt FROM events, event_types, event_cats, event_days WHERE events.event_id =3D event_days.event_id and events.etype_id =3D event_types.etype_id AND event_types.ecat_id =3D event_cats.ecat_id AND event_days.event_day BETWEEN '2003-04-15' AND '2003-04-16 10:00'=20 ORDER BY cflt LIMIT 1) AS conflict FROM users=20 WHERE EXISTS (SELECT teams_users.user_id FROM teams_users JOIN teams_tree ON teams_users.team_id =3D teams_tree.team_id WHERE teams_tree.treeno BETWEEN 3 and 4 AND teams_users.user_id =3D users.user_id) AND users.status > 0 AND NOT EXISTS (SELECT staff_id FROM event_staff WHERE event_id =3D 3272 AND staff_id =3D users.user_id)=20 ORDER BY conflict, users.lname, atty_name; NOTICE: QUERY PLAN: Sort (cost=3D3595.55..3595.55 rows=3D12 width=3D87) (actual time=3D547.89.= .547.91=20 rows=3D41 loops=3D1) -> Seq Scan on users (cost=3D0.00..3595.33 rows=3D12 width=3D87) (actua= l=20 time=3D13.50..547.59 rows=3D41 loops=3D1) SubPlan -> Limit (cost=3D54.03..54.03 rows=3D1 width=3D46) (actual=20 time=3D13.14..13.14 rows=3D1 loops=3D41) -> Sort (cost=3D54.03..54.03 rows=3D1 width=3D46) (actual= =20 time=3D13.13..13.13 rows=3D2 loops=3D41) -> Hash Join (cost=3D52.77..54.02 rows=3D1 width=3D= 46)=20 (actual time=3D5.09..12.94 rows=3D95 loops=3D41) -> Seq Scan on event_cats (cost=3D0.00..1.16= =20 rows=3D16 width=3D6) (actual time=3D0.01..0.05 rows=3D16 loops=3D41) -> Hash (cost=3D52.77..52.77 rows=3D1 width= =3D40)=20 (actual time=3D4.72..4.72 rows=3D0 loops=3D41) -> Hash Join (cost=3D49.94..52.77 rows= =3D1=20 width=3D40) (actual time=3D4.19..4.59 rows=3D95 loops=3D41) -> Seq Scan on event_types=20=20 (cost=3D0.00..2.54 rows=3D54 width=3D8) (actual time=3D0.01..0.12 rows=3D54= loops=3D41) -> Hash (cost=3D49.93..49.93 rows= =3D5=20 width=3D32) (actual time=3D4.10..4.10 rows=3D0 loops=3D41) -> Nested Loop=20=20 (cost=3D0.00..49.93 rows=3D5 width=3D32) (actual time=3D0.16..3.95 rows=3D9= 5 loops=3D41) -> Seq Scan on event_d= ays=20=20 (cost=3D0.00..25.00 rows=3D5 width=3D4) (actual time=3D0.12..2.31 rows=3D95= loops=3D41) -> Index Scan using=20 events_pkey on events (cost=3D0.00..4.97 rows=3D1 width=3D28) (actual=20 time=3D0.01..0.01 rows=3D1 loops=3D3895) -> Nested Loop (cost=3D0.00..19.47 rows=3D1 width=3D12) (actual= =20 time=3D0.04..0.04 rows=3D0 loops=3D147) -> Index Scan using idx_teams_tree_node on teams_tree=20= =20 (cost=3D0.00..8.58 rows=3D2 width=3D4) (actual time=3D0.01..0.02 rows=3D2 l= oops=3D147) -> Index Scan using teams_users_pk on teams_users=20=20 (cost=3D0.00..4.83 rows=3D1 width=3D8) (actual time=3D0.01..0.01 rows=3D0 l= oops=3D252) -> Index Scan using event_staff_table_pk on event_staff=20=20 (cost=3D0.00..4.95 rows=3D1 width=3D4) (actual time=3D0.01..0.01 rows=3D0 l= oops=3D41) Total runtime: 548.20 msec --=20 -Josh Berkus Aglio Database Solutions San Francisco