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 1C9B130EB94 for ; Wed, 25 Jun 2003 14:48:53 +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 48038-10 for ; Wed, 25 Jun 2003 11:48:42 -0300 (ADT) Received: from mwinf0302.wanadoo.fr (smtp6.wanadoo.fr [193.252.22.28]) by svr1.postgresql.org (Postfix) with ESMTP id 371D330FAF4 for ; Wed, 25 Jun 2003 11:48:42 -0300 (ADT) Received: from Paris (unknown [80.15.41.168]) by mwinf0302.wanadoo.fr (SMTP Server) with ESMTP id 2C283C00026B; Wed, 25 Jun 2003 16:48:42 +0200 (CEST) Reply-To: From: "Michael Mattox" To: =?iso-8859-1?Q?SZUCS_G=E1bor?= , Subject: Re: How to optimize monstrous query, sorts instead of using index Date: Wed, 25 Jun 2003 16:48:46 +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: <008e01c33b24$49f091b0$0403a8c0@fejleszt4> X-Archive-Number: 200306/167 X-Sequence-Number: 2251 > Finally, decided to do an ad-hoc adjustment. Try this, or (wild guess) try > to completely eliminate the WHERE part by subselects on ms and monitorx. > > This may be faster, slower, or even give different results, based > on whether > I guessed the 1:N relationships right or not. It's much slower but I appreciate you taking the time to try. I'm pretty new to SQL so I must admin this query is very confusing for me. I'm using Java Data Objects (JDO, an O/R mapping framework) but the implementation I'm using (Kodo) isn't smart enough to do all the joins efficiently, which is why I had to rewrite this query by hand. Here's the output: 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 LEFT JOIN monitorx ON (monitorx.jdoidx = ms.monitorx) LEFT JOIN monitorstatus_statusitemsx ms_si ON (ms.jdoidx = ms_si.jdoidx) LEFT JOIN monitorstatusitemlistd8ea58a5x msil ON (ms_si.statusitemsx = msil.jdoidx) LEFT JOIN monitorstatusitemx msi ON (msil.statusitemlistx = msi.jdoidx) where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352' AND ms.datex >= '2003-06-20 08:57:21.36' AND ms.datex <= '2003-06-29 08:57:21.36'; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------------- Merge Join (cost=1006209.47..1283529.68 rows=751715 width=826) Merge Cond: ("outer".jdoidx = "inner".statusitemlistx) -> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx msi (cost=0.00..247679.64 rows=6595427 width=665) -> Sort (cost=1006209.47..1008088.76 rows=751715 width=161) Sort Key: msil.statusitemlistx -> Merge Join (cost=697910.17..864079.59 rows=751715 width=161) Merge Cond: ("outer".jdoidx = "inner".statusitemsx) -> Index Scan using monitorstatusitejdoid7db0befci on monitorstatusitemlistd8ea58a5x msil (cost=0.00..136564.80 rows=6595427 width=16) -> Sort (cost=697910.17..699789.46 rows=751715 width=145) Sort Key: ms_si.statusitemsx -> Merge Join (cost=385727.49..561594.96 rows=751715 width=145) Merge Cond: ("outer".jdoidx = "inner".jdoidx) -> Index Scan using monitorstatus_stjdoidb742c9b3i on monitorstatus_statusitemsx ms_si (cost=0.00..146268.80 rows=6595427 width=16) -> Sort (cost=385727.49..387606.78 rows=751715 width=129) Sort Key: ms.jdoidx -> Hash Join (cost=155.66..255240.65 rows=751715 width=129) Hash Cond: ("outer".monitorx = "inner".jdoidx) Filter: ("inner".idx = 'M-TEST_1444-TEST_00_10560561260561463219352'::character varying) -> Seq Scan on monitorstatusx ms (cost=0.00..240050.69 rows=751715 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)) -> Hash (cost=151.93..151.93 rows=1493 width=46) -> Seq Scan on monitorx (cost=0.00..151.93 rows=1493 width=46) (22 rows) veriguard=#