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 0AB0C30FBEC for ; Wed, 25 Jun 2003 11:54:13 +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 31683-06 for ; Wed, 25 Jun 2003 08:54:02 -0300 (ADT) Received: from tomts5-srv.bellnexxia.net (tomts5.bellnexxia.net [209.226.175.25]) by svr1.postgresql.org (Postfix) with ESMTP id 046B530FB82 for ; Wed, 25 Jun 2003 08:54:02 -0300 (ADT) Received: from [192.168.1.200] ([216.209.81.133]) by tomts5-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030625115401.JLRC23206.tomts5-srv.bellnexxia.net@[192.168.1.200]>; Wed, 25 Jun 2003 07:54:01 -0400 Subject: Re: How to optimize monstrous query, sorts instead of using index 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="=-/QG6TYDtMAHPfbV+e0Sb" Message-Id: <1056542059.69651.73.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 25 Jun 2003 07:54:20 -0400 X-Archive-Number: 200306/157 X-Sequence-Number: 2241 --=-/QG6TYDtMAHPfbV+e0Sb Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Is this 7.3.x? Can we see explain analyze output for the query? On Wed, 2003-06-25 at 07:46, Michael Mattox wrote: > I've used indexes to speed up my queries but this query escapes me. I'm > curious if someone can suggest an index or a way to modify the query to u= se > the index. The query is: >=20 > select ms.averageconnecttimex as ms_averageconnecttime, ms.averageduratio= nx > 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 =3D > 'M-TEST_1444-TEST_00_10560561260561463219352' AND monitorx.jdoidx =3D > ms.monitorx AND ms.datex >=3D '2003-06-20 08:57:21.36' AND ms.datex <=3D > '2003-06-29 08:57:21.36' AND ms.jdoidx =3D monitorstatus_statusitemsx.jdo= idx > AND monitorstatus_statusitemsx.statusitemsx =3D > monitorstatusitemlistd8ea58a5x.jdoidx AND > monitorstatusitemlistd8ea58a5x.statusitemlistx =3D msi.jdoidx ORDER BY ms= _date > DESC; >=20 > Here is the result of explain: >=20 > Sort (cost=3D9498.85..9500.16 rows=3D525 width=3D788) > Sort Key: ms.datex > -> Nested Loop (cost=3D0.00..9475.15 rows=3D525 width=3D788) > -> Nested Loop (cost=3D0.00..7887.59 rows=3D525 width=3D123) > -> Nested Loop (cost=3D0.00..6300.03 rows=3D525 width=3D= 107) > -> Nested Loop (cost=3D0.00..4712.02 rows=3D525 wi= dth=3D91) > -> Index Scan using monitorx_id_index on > monitorx (cost=3D0.00..5.37 rows=3D1 width=3D8) > Index Cond: (idx =3D > 'M-TEST_1444-TEST_00_10560561260561463219352'::character varying) > -> Index Scan using monitorstatusxmonitori on > monitorstatusx ms (cost=3D0.00..4695.65 rows=3D880 width=3D83) > Index Cond: ("outer".jdoidx =3D ms.monit= orx) > Filter: ((datex >=3D '2003-06-20 > 08:57:21.36'::timestamp without time zone) AND (datex <=3D '2003-06-29 > 08:57:21.36'::timestamp without time zone)) > -> Index Scan using monitorstatus_stjdoidb742c9b3i = on > monitorstatus_statusitemsx (cost=3D0.00..3.01 rows=3D1 width=3D16) > Index Cond: ("outer".jdoidx =3D > monitorstatus_statusitemsx.jdoidx) > -> Index Scan using monitorstatusitejdoid7db0befci on > monitorstatusitemlistd8ea58a5x (cost=3D0.00..3.01 rows=3D1 width=3D16) > Index Cond: ("outer".statusitemsx =3D > monitorstatusitemlistd8ea58a5x.jdoidx) > -> Index Scan using monitorstatusitemx_pkey on monitorstatusite= mx > msi (cost=3D0.00..3.01 rows=3D1 width=3D665) > Index Cond: ("outer".statusitemlistx =3D msi.jdoidx) > (17 rows) >=20 > As you can see, it's doing a sort on ms.datex. I created an index on the > monitorstatusx (ms) table for the datex, but it doesn't use it. Is it > possible to create an index to prevent this sort? >=20 > Thanks, > Michael >=20 >=20 > Michael Mattox > cunparis@yahoo.fr / http://www.advweb.com/michael >=20 >=20 >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-/QG6TYDtMAHPfbV+e0Sb 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) iD4DBQA++Y1r6DETLow6vwwRAridAJY201Vf1kqnJch5pRMVlr+A9nn8AJ0Ydjnl RThYtKm5JZJu5kdjgcddMg== =yRh/ -----END PGP SIGNATURE----- --=-/QG6TYDtMAHPfbV+e0Sb--