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 3F8B330FB6F for ; Wed, 25 Jun 2003 12:12:08 +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 26187-09 for ; Wed, 25 Jun 2003 09:11:57 -0300 (ADT) Received: from tomts11-srv.bellnexxia.net (tomts11.bellnexxia.net [209.226.175.55]) by svr1.postgresql.org (Postfix) with ESMTP id 90CC230ECF0 for ; Wed, 25 Jun 2003 09:11:52 -0300 (ADT) Received: from [192.168.1.200] ([216.209.81.133]) by tomts11-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030625121150.IHIF11106.tomts11-srv.bellnexxia.net@[192.168.1.200]>; Wed, 25 Jun 2003 08:11:50 -0400 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="=-P3a7PTZtrI2Y3WlL8mjs" Message-Id: <1056543129.69651.82.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 25 Jun 2003 08:12:09 -0400 X-Archive-Number: 200306/159 X-Sequence-Number: 2243 --=-P3a7PTZtrI2Y3WlL8mjs Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > Here's the output of explain analyze. The query typically takes 0-4 seco= nds > depending on the time frame. It's run very frequently especially to proc= ess > the nightly reports. The plan picked seems reasonable (estimated costs / tuples is close to actual). I think the biggest hit is this index scan. Thats a substantial cost to pull out less than a thousand lines: -> Index Scan using monitorstatusxmonitori on monitorstatusx ms (cost=3D0.00..4695.65 rows=3D880 width=3D83) (actual time=3D40.17..1868.12 rows=3D625 loops=3D1) Index Cond: ("outer".jdoidx =3D ms.monitorx) 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)) Are jdoidx and monitorx integers? You might try a multi-column index on (ms.monitorx, ms.datex). Are monitorx assigned roughly ordered by date? It must be, otherwise the sort step would not be so cheap (hardly any impact on the query -- see actual cost number). The multi-column index above should give you a bit of a boost. Depending on the data in the table, the index (ms.datex, monitorx) may give better results along with a single index on (ms.monitorx) as you currently have. It's not very likely though. --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-P3a7PTZtrI2Y3WlL8mjs 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++ZGY6DETLow6vwwRAgkkAJ4+eIEVghwMdvycwKFiMMY9L6c+agCeKKL0 M5M19SZ7urYrmgJptI4Msbg= =Lvsm -----END PGP SIGNATURE----- --=-P3a7PTZtrI2Y3WlL8mjs--