X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CB31CD1B44C for ; Fri, 9 Jan 2004 03:29:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 27922-09 for ; Thu, 8 Jan 2004 23:28:59 -0400 (AST) Received: from pomeray.duluoz.net (12-241-192-70.client.attbi.com [12.241.192.70]) by svr1.postgresql.org (Postfix) with ESMTP id 89B5FD1B4D4 for ; Thu, 8 Jan 2004 23:27:17 -0400 (AST) Received: (qmail 6232 invoked by uid 1000); 9 Jan 2004 03:27:20 -0000 Date: Thu, 8 Jan 2004 19:27:16 -0800 From: Mike Glover To: Bradley Tate Cc: pgsql-performance@postgresql.org Subject: Re: Slow query problem Message-Id: <20040108192716.26c4b369.mpg4@duluoz.net> In-Reply-To: <3FFCF005.70108@objectmastery.com> References: <3FFCF005.70108@objectmastery.com> X-Mailer: Sylpheed version 0.9.0claws (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="=.U(0Zldy2q1+SH7" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/91 X-Sequence-Number: 5266 --=.U(0Zldy2q1+SH7 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit On Thu, 08 Jan 2004 16:52:05 +1100 Bradley Tate wrote: > Am I correct in interpreting that most time was spent doing the > sorting? looks so. your table is about 70MB total size, and its getting loaded completely into memory (you have 12000 * 8k = 96M available). 26s to load 70MB from disk seems reasonable. The rest of the time is used for sorting. > Explain confuses the heck out of me and any help on how I could make > this run faster would be gratefully received. > You should bump sort_mem as high as you can stand. with only 8MB sort memory available, you're swapping intermediate sort pages to disk -- a lot. Try the query with sort_mem set to 75MB (to do the entire sort in memory). -mike > Cheers, > > Bradley. > > > ---------------------------(end of > broadcast)--------------------------- TIP 8: explain analyze is your > friend -- Mike Glover Key ID BFD19F2C --=.U(0Zldy2q1+SH7 Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQE//h+YZrNpxr/RnywRAqFgAJ940IieUUxhaZesAzC4Rv933yVCDACdH3DU 4ylBJp0SmxnCdnuks1v54Jg= =VDUZ -----END PGP SIGNATURE----- --=.U(0Zldy2q1+SH7--