X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id B755BB43A58 for ; Thu, 3 Jul 2003 15:58:33 +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 26736-06 for ; Thu, 3 Jul 2003 12:58:11 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 2870CB43A3B for ; Thu, 3 Jul 2003 12:57:24 -0300 (ADT) Received: from tomts24-srv.bellnexxia.net (tomts24.bellnexxia.net [209.226.175.187]) by www.postgresql.com (Postfix) with ESMTP id 8FBA2CF7835 for ; Thu, 3 Jul 2003 09:29:31 -0300 (ADT) Received: from [192.168.1.200] ([216.209.81.133]) by tomts24-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030703122545.HKXZ23872.tomts24-srv.bellnexxia.net@[192.168.1.200]>; Thu, 3 Jul 2003 08:25:45 -0400 Subject: Re: How to optimize monstrous query, sorts instead of From: Rod Taylor To: Ang Chin Han Cc: michael.mattox@verideon.com, Postgresql Performance In-Reply-To: <3F038DF5.70602@bytecraft.com.my> References: <3F038DF5.70602@bytecraft.com.my> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-W3k13ytIy3LZGIZSI2A3" Message-Id: <1057235173.30368.32.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 03 Jul 2003 08:26:13 -0400 X-Archive-Number: 200307/29 X-Sequence-Number: 2322 --=-W3k13ytIy3LZGIZSI2A3 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > CREATE VIEW foo AS {complex_slow_query}; >=20 > SET random_page_cost =3D 1.5; EXPLAIN ANALYZE SELECT * FROM foo; >=20 > Note the time taken. Repeat a few times to get the average. You pulled everything off disk and tossed it into memory with the first run so the results will NOT match your normal situation (some data on disk, some cached in memory) for your second run and further runs unless there is a LONG timeframe between runs. That said, if you test with several other queries and get the same results, it's probably good enough for your system. --=-W3k13ytIy3LZGIZSI2A3 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/BCDk6DETLow6vwwRAqHkAJoCRGyPk7aylVX2+kk9MWAuWCTxIgCePDc3 rlGhTwLwMtbOSgqCW/lS3K4= =TJrl -----END PGP SIGNATURE----- --=-W3k13ytIy3LZGIZSI2A3--