X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 21176D1B4F8 for ; Tue, 21 Oct 2003 14:51:22 +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 73743-08 for ; Tue, 21 Oct 2003 11:50:55 -0300 (ADT) Received: from haeb.noc.uk.easynet.net (haeb.noc.uk.easynet.net [195.40.7.191]) by svr1.postgresql.org (Postfix) with ESMTP id 44FE3D1B518 for ; Tue, 21 Oct 2003 11:50:50 -0300 (ADT) Received: (from haeb@localhost) by haeb.noc.uk.easynet.net (8.9.3/8.9.1) id PAA15768; Tue, 21 Oct 2003 15:50:48 +0100 (BST) (envelope-from haeb) Message-Id: <200310211450.PAA15768@haeb.noc.uk.easynet.net> Subject: Re: Performance weirdness with/without vacuum analyze In-Reply-To: <3F951FC8.2090906@myrealbox.com> from Shridhar Daithankar at "Oct 21, 2003 5:30: 8 pm" To: shridhar_daithankar@myrealbox.com (Shridhar Daithankar) Date: Tue, 21 Oct 2003 15:50:48 +0100 (BST) Cc: harry.broomhall@uk.easynet.net, pgsql-performance@postgresql.org From: Harry Broomhall Reply-To: "Harry Broomhall" X-Mailer: ELM [version 2.4ME+ PL43 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/528 X-Sequence-Number: 4276 Shridhar Daithankar writes: First - many thanks for your suggestions and pointers to further info. I have been trying some of them with some interesting results! > Harry Broomhall wrote: > > #effective_cache_size = 1000 # typically 8KB each > > #random_page_cost = 4 # units are one sequential page fetch cost > > You must tune the first one at least. Try > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these > parameters. Changing effective_cache_size seemed to have very little effect. I took it in steps up to 300MB (the machine has 640MB memory), and the differences in speed were less than 10%. [SNIP] > > What happens if you turn off hash joins? This makes the non vacuum version about 40% slower, and the vacuum version to the same speed (i.e. about 4X faster than it had been!). > Also bump sort memory to something > good.. around 16MB and see what difference does it make to performance.. This was interesting. Taking it to 10MB made a slight improvement. Up to 20MB and the vacuum case improved by 5X speed, but the non-vacuum version slowed down. Putting it up to 40MB slowed both down again. I will need to test with some of the other scripts and functions I have written, but it looks as if selective use of more sort memory will be useful. Regards, Harry.