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 17CCD30DD20 for ; Wed, 25 Jun 2003 13:35: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 37388-10 for ; Wed, 25 Jun 2003 10:35:42 -0300 (ADT) Received: from tomts5-srv.bellnexxia.net (tomts5.bellnexxia.net [209.226.175.25]) by svr1.postgresql.org (Postfix) with ESMTP id 4FBA530FB81 for ; Wed, 25 Jun 2003 10:35:42 -0300 (ADT) Received: from [10.0.2.10] ([216.208.117.7]) by tomts5-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030625133542.NPEC23206.tomts5-srv.bellnexxia.net@[10.0.2.10]>; Wed, 25 Jun 2003 09:35:42 -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="=-jpw/uoCwjEmenDtiuz7j" Message-Id: <1056548165.25587.23.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 25 Jun 2003 09:36:06 -0400 X-Archive-Number: 200306/162 X-Sequence-Number: 2246 --=-jpw/uoCwjEmenDtiuz7j Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > > You might try a multi-column index on (ms.monitorx, ms.datex). >=20 > Just tried it, it didn't prevent the sort. But it sounds like the sort > isn't the problem, correct? The sort isn't actually doing any sorting, so it's virtually free. The sort is taking less than 3ms as the data is already 99% sorted due to the correlation between datex and monitorx. For similar reasons, the datex index will not be used, as it has no advantage to being used. > -> Index Scan using > monitorstatusx_datex_monitorx_index on monitorstatusx ms > (cost=3D0.00..1159.33 rows=3D890 width=3D83) (actual time=3D0.19..1287.02= rows=3D628 > loops=3D1) > Index Cond: (("outer".jdoidx =3D ms.moni= torx) > AND (ms.datex >=3D '2003-06-20 08:57:21.36'::timestamp without time zone)= AND > (ms.datex <=3D '2003-06-29 08:57:21.36'::timestamp without time zone)) You can see that it used the new multi-key index for both items, rather than finding for monitorx, then filtering out unwanted results by datex. It doesn't appear to have made much difference (looks like data was partially cached for this new run), but it changed a bit for the better. I'm afraid thats the best I can do on the query itself I think. Oh, and using tables in your where clause that aren't in the from clause is non-portable and often hides bugs: from monitorstatusx ms , monitorstatusitemx msi where monitorx.idx =3D 'M-TEST_1444-TEST_00_10560561260561463219352' Are you sure you sure you don't have any duplicated constraints by pulling information in from other tables that you don't need to?=20 Removing some of those nested loops would make a significant impact to the results. --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-jpw/uoCwjEmenDtiuz7j 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++aVF6DETLow6vwwRAlDkAJ4wxNIxrf+J6rqefK3Xi1hTfWN2pQCfShR+ dHGdJ8TUvcgZc4/4u8LY2bg= =SUnl -----END PGP SIGNATURE----- --=-jpw/uoCwjEmenDtiuz7j--