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 A266130FC4B for ; Wed, 25 Jun 2003 15:09:41 +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 53857-08 for ; Wed, 25 Jun 2003 12:09:24 -0300 (ADT) Received: from mwinf0303.wanadoo.fr (smtp6.wanadoo.fr [193.252.22.28]) by svr1.postgresql.org (Postfix) with ESMTP id 658AF30FC41 for ; Wed, 25 Jun 2003 12:09:18 -0300 (ADT) Received: from Paris (ABoulogne-110-1-3-168.w80-15.abo.wanadoo.fr [80.15.41.168]) by mwinf0303.wanadoo.fr (SMTP Server) with ESMTP id E55BF500083D; Wed, 25 Jun 2003 17:09:16 +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 17:09:21 +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: <1056551304.25587.52.camel@jester> X-Archive-Number: 200306/168 X-Sequence-Number: 2252 With a slight correction (you had m & mx so I changed them to be all mx, I hope this is what you intended) this query works. It's exactly the same speed, but it doesn't give me the warnings I was getting: NOTICE: Adding missing FROM-clause entry for table "monitorx" NOTICE: Adding missing FROM-clause entry for table "monitorstatus_statusitemsx" NOTICE: Adding missing FROM-clause entry for table "monitorstatusitemlistd8ea58a5x" I never knew what those were from, I even searched Google trying to find out and I couldn't understand it so I gave up. Thanks for pointing this out for me, and thanks for fixing my query. Michael > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Rod Taylor > Sent: Wednesday, June 25, 2003 4:28 PM > To: michael.mattox@verideon.com > Cc: Postgresql Performance > Subject: Re: [PERFORM] How to optimize monstrous query, sorts instead of > > > > 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? > > By using it aliased and non-aliased (2 different references to the same > table) you've caused it to join itself. > > Try this: > > 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 monitorstatusx AS ms > , monitorstatusitemx AS msi > > , monitorx AS mx > , monitorstatus_statusitemsx AS mssisx > , monitorstatusitemlistd8ea58a5x AS litem > > WHERE ms.jdoidx = mssisx.jdoidx > AND mssisx.statusitemsx = litem.jdoidx > AND litem.statusitemlistx = msi.jdoidx > AND mx.jdoidx = ms.monitorx > AND ms.datex BETWEEN '2003-06-20 08:57:21.36' > AND '2003-06-29 08:57:21.36' > AND m.idx = 'M-TEST_1444-TEST_00_10560561260561463219352' > > ORDER BY ms.datex DESC; > > -- > Rod Taylor > > PGP Key: http://www.rbt.ca/rbtpub.asc >