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 DC185D1B518 for ; Tue, 21 Oct 2003 12:37:08 +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 61491-04 for ; Tue, 21 Oct 2003 09:36:41 -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 D4131D1B520 for ; Tue, 21 Oct 2003 09:36:28 -0300 (ADT) Received: (from haeb@localhost) by haeb.noc.uk.easynet.net (8.9.3/8.9.1) id NAA15239; Tue, 21 Oct 2003 13:35:50 +0100 (BST) (envelope-from haeb) Message-Id: <200310211235.NAA15239@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 13:35:50 +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/523 X-Sequence-Number: 4271 Shridhar Daithankar writes: > 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. Wow. Many thanks for the pointer. I'm going to be spending some time trying to get my head around all of that! [SNIP] > > Total runtime: 80408.42 msec > > (12 rows) > > You are lucky to get a better plan here because planner is way off w.r.t > estimated number of rows. Yes! I thought that. Which was why I was so surprised at the difference. > > > > And now the case *with* the vacuum analyze: > > [SNIP] > > What happens if you turn off hash joins? Also bump sort memory to something > good.. around 16MB and see what difference does it make to performance.. Lots of things to try there..... It will probably take me some time . Regards, Harry.