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 0CD6330EB94 for ; Wed, 25 Jun 2003 14:28:37 +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 46480-07 for ; Wed, 25 Jun 2003 11:28:26 -0300 (ADT) Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106]) by svr1.postgresql.org (Postfix) with ESMTP id 6D63B30DD20 for ; Wed, 25 Jun 2003 11:28:26 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 28AAF10257; Wed, 25 Jun 2003 10:21:42 -0400 (EDT) Received: from [10.0.2.10] (unknown [10.0.2.10]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 3959A1024D; Wed, 25 Jun 2003 10:21:40 -0400 (EDT) Subject: Re: How to optimize monstrous query, sorts instead of From: Rod Taylor To: michael.mattox@verideon.com Cc: Postgresql Performance In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-LHvEiE/xCZxS6sxjzJM5" Message-Id: <1056551304.25587.52.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 25 Jun 2003 10:28:25 -0400 X-Virus-Scanned: by AMaViS perl-11 X-Archive-Number: 200306/165 X-Sequence-Number: 2249 --=-LHvEiE/xCZxS6sxjzJM5 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > 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 performan= ce? 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 =3D mssisx.jdoidx AND mssisx.statusitemsx =3D litem.jdoidx AND litem.statusitemlistx =3D msi.jdoidx AND mx.jdoidx =3D ms.monitorx AND ms.datex BETWEEN '2003-06-20 08:57:21.36' AND '2003-06-29 08:57:21.36' AND m.idx =3D 'M-TEST_1444-TEST_00_10560561260561463219352' ORDER BY ms.datex DESC; --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-LHvEiE/xCZxS6sxjzJM5 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA++bGH6DETLow6vwwRAgrHAJ47GFg/7kg6zY36gxyEWmqhXRjoIwCfRFqw BKdXAP3pac6FQa3y0fJx2Nk= =19Ve -----END PGP SIGNATURE----- --=-LHvEiE/xCZxS6sxjzJM5--