X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 6A89930FC4B for ; Wed, 25 Jun 2003 14:10:27 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 45901-04 for ; Wed, 25 Jun 2003 11:09:56 -0300 (ADT) Received: from mwinf0301.wanadoo.fr (smtp6.wanadoo.fr [193.252.22.28]) by svr1.postgresql.org (Postfix) with ESMTP id 104A630FB70 for ; Wed, 25 Jun 2003 11:09:55 -0300 (ADT) Received: from Paris (ABoulogne-110-1-3-168.w80-15.abo.wanadoo.fr [80.15.41.168]) by mwinf0301.wanadoo.fr (SMTP Server) with ESMTP id 795744007BA; Wed, 25 Jun 2003 16:09:54 +0200 (CEST) Reply-To: From: "Michael Mattox" To: "Rod Taylor" Cc: "Postgresql Performance" Subject: Re: How to optimize monstrous query, sorts instead of Date: Wed, 25 Jun 2003 16:09:59 +0200 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal In-Reply-To: <1056548165.25587.23.camel@jester> X-Archive-Number: 200306/163 X-Sequence-Number: 2247 > Oh, and using tables in your where clause that aren't in the from clause > is non-portable and often hides bugs: > > from monitorstatusx ms > , monitorstatusitemx msi > where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352' > > Are you sure you sure you don't have any duplicated constraints by > pulling information in from other tables that you don't need to? > Removing some of those nested loops would make a significant impact to > the results. I didn't notice that before, thanks for pointing that out. I just tried adding monitorx.idx to the select and it ended up making my query take several minutes long. Any ideas how I can fix this and keep my performance? new query: veriguard=# explain select m.idx, ms.averageconnecttimex as ms_averageconnecttime, ms.averagedurationx as ms_averageduration, ms.datex as ms_date, ms.idx as ms_id, ms.statusstringx as ms_statusstring, ms.statusx as ms_status, msi.actualcontentx as msi_actualcontent, msi.connecttimex as msi_connecttime, msi.correctcontentx as msi_correctcontent, msi.datex as msi_date, msi.descriptionx as msi_description, msi.durationx as msi_duration, msi.errorcontentx as msi_errorcontent, msi.idx as msi_id, msi.monitorlocationx as msi_monitorlocation, msi.statusstringx as msi_statusstring, msi.statusx as msi_status from monitorx m, monitorstatusx ms, monitorstatusitemx msi where m.idx = 'M-TEST_1444-TEST_00_10560561260561463219352' AND monitorx.jdoidx = ms.monitorx AND ms.datex >= '2003-06-20 08:57:21.36' AND ms.datex <= '2003-06-29 08:57:21.36' AND ms.jdoidx = monitorstatus_statusitemsx.jdoidx AND monitorstatus_statusitemsx.statusitemsx = monitorstatusitemlistd8ea58a5x.jdoidx AND monitorstatusitemlistd8ea58a5x.statusitemlistx = msi.jdoidx ORDER BY ms_date DESC; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------------- Sort (cost=1653384.42..1655402.97 rows=807418 width=826) Sort Key: ms.datex -> Hash Join (cost=820308.66..1112670.42 rows=807418 width=826) Hash Cond: ("outer".monitorx = "inner".jdoidx) -> Merge Join (cost=820132.71..1098364.65 rows=807418 width=780) Merge Cond: ("outer".jdoidx = "inner".statusitemlistx) -> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx msi (cost=0.00..247616.27 rows=6596084 width=665) -> Sort (cost=820132.71..822151.59 rows=807554 width=115) Sort Key: monitorstatusitemlistd8ea58a5x.statusitemlistx -> Hash Join (cost=461310.87..685820.13 rows=807554 width=115) Hash Cond: ("outer".jdoidx = "inner".statusitemsx) -> Seq Scan on monitorstatusitemlistd8ea58a5x (cost=0.00..104778.90 rows=6597190 width=16) -> Hash (cost=447067.98..447067.98 rows=807554 width=99) -> Merge Join (cost=0.00..447067.98 rows=807554 width=99) Merge Cond: ("outer".jdoidx = "inner".jdoidx) -> Index Scan using monitorstatusx_pkey on monitorstatusx ms (cost=0.00..272308.56 rows=811754 width=83) Filter: ((datex >= '2003-06-20 08:57:21.36'::timestamp without time zone) AND (datex <= '2003-06-29 08:57:21.36'::timestamp without time zone)) -> Index Scan using monitorstatus_stjdoidb742c9b3i on monitorstatus_statusitemsx (cost=0.00..146215.58 rows=6596680 width=16) -> Hash (cost=172.22..172.22 rows=1493 width=46) -> Nested Loop (cost=0.00..172.22 rows=1493 width=46) -> Index Scan using monitorx_id_index on monitorx m (cost=0.00..5.36 rows=1 width=38) Index Cond: (idx = 'M-TEST_1444-TEST_00_10560561260561463219352'::character varying) -> Seq Scan on monitorx (cost=0.00..151.93 rows=1493 width=8) (23 rows) old query: veriguard=# explain select ms.averageconnecttimex as ms_averageconnecttime, ms.averagedurationx as ms_averageduration, ms.datex as ms_date, ms.idx as ms_id, ms.statusstringx as ms_statusstring, ms.statusx as ms_status, msi.actualcontentx as msi_actualcontent, msi.connecttimex as msi_connecttime, msi.correctcontentx as msi_correctcontent, msi.datex as msi_date, msi.descriptionx as msi_description, msi.durationx as msi_duration, msi.errorcontentx as msi_errorcontent, msi.idx as msi_id, msi.monitorlocationx as msi_monitorlocation, msi.statusstringx as msi_statusstring, msi.statusx as msi_status from monitorstatusx ms, monitorstatusitemx msi where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352' AND monitorx.jdoidx = ms.monitorx AND ms.datex >= '2003-06-20 08:57:21.36' AND ms.datex <= '2003-06-29 08:57:21.36' AND ms.jdoidx = monitorstatus_statusitemsx.jdoidx AND monitorstatus_statusitemsx.statusitemsx = monitorstatusitemlistd8ea58a5x.jdoidx AND monitorstatusitemlistd8ea58a5x.statusitemlistx = msi.jdoidx ORDER BY ms_date DESC; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------- Sort (cost=9590.52..9591.87 rows=541 width=788) Sort Key: ms.datex -> Nested Loop (cost=0.00..9565.97 rows=541 width=788) -> Nested Loop (cost=0.00..7929.22 rows=541 width=123) -> Nested Loop (cost=0.00..6292.48 rows=541 width=107) -> Nested Loop (cost=0.00..4647.22 rows=544 width=91) -> Index Scan using monitorx_id_index on monitorx (cost=0.00..5.36 rows=1 width=8) Index Cond: (idx = 'M-TEST_1444-TEST_00_10560561260561463219352'::character varying) -> Index Scan using monitorstatusxmonitori on monitorstatusx ms (cost=0.00..4630.29 rows=926 width=83) Index Cond: ("outer".jdoidx = ms.monitorx) Filter: ((datex >= '2003-06-20 08:57:21.36'::timestamp without time zone) AND (datex <= '2003-06-29 08:57:21.36'::timestamp without time zone)) -> Index Scan using monitorstatus_stjdoidb742c9b3i on monitorstatus_statusitemsx (cost=0.00..3.01 rows=1 width=16) Index Cond: ("outer".jdoidx = monitorstatus_statusitemsx.jdoidx) -> Index Scan using monitorstatusitejdoid7db0befci on monitorstatusitemlistd8ea58a5x (cost=0.00..3.01 rows=1 width=16) Index Cond: ("outer".statusitemsx = monitorstatusitemlistd8ea58a5x.jdoidx) -> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx msi (cost=0.00..3.01 rows=1 width=665) Index Cond: ("outer".statusitemlistx = msi.jdoidx) (17 rows) veriguard=#