Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id E8BF91337B83 for ; Thu, 3 Feb 2011 09:41:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 00424-06 for ; Thu, 3 Feb 2011 13:41:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh5.mail.rice.edu (mh5.mail.rice.edu [128.42.199.32]) by mail.postgresql.org (Postfix) with ESMTP id 39B0C1337B2F for ; Thu, 3 Feb 2011 09:41:45 -0400 (AST) Received: from mh5.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh5.mail.rice.edu (Postfix) with ESMTP id 0D2DF28F7EB; Thu, 3 Feb 2011 07:41:45 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh5.mail.rice.edu, auth channel Received: from mh5.mail.rice.edu ([127.0.0.1]) by mh5.mail.rice.edu (mh5.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id l2z1whQMevvr; Thu, 3 Feb 2011 07:41:44 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh5.mail.rice.edu (Postfix) with ESMTP id CB3CE28F7E6; Thu, 3 Feb 2011 07:41:44 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id F2BE52D5A6; Thu, 3 Feb 2011 07:41:43 -0600 (CST) Date: Thu, 3 Feb 2011 07:41:42 -0600 From: Kenneth Marshall To: david@lang.hm Cc: Vitalii Tymchyshyn , Robert Haas , Jon Nelson , Mladen Gogala , Andrew Dunstan , Bruce Momjian , Tom Lane , Craig Ringer , "pgsql-performance@postgresql.org" Subject: Re: [HACKERS] Slow count(*) again... Message-ID: <20110203134142.GO24931@aart.is.rice.edu> References: <201102012247.p11Ml6u02682@momjian.us> <4D48914B.4050403@dunslane.net> <4D489AD1.1010105@vmsinfo.com> <4D499E55.4020107@vmsinfo.com> <4D4A7B69.2020704@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/71 X-Sequence-Number: 42209 On Thu, Feb 03, 2011 at 02:11:58AM -0800, david@lang.hm wrote: > On Thu, 3 Feb 2011, Vitalii Tymchyshyn wrote: > >> 02.02.11 20:32, Robert Haas ???????(??): >>> Yeah. Any kind of bulk load into an empty table can be a problem, >>> even if it's not temporary. When you load a bunch of data and then >>> immediately plan a query against it, autoanalyze hasn't had a chance >>> to do its thing yet, so sometimes you get a lousy plan. >> >> May be introducing something like 'AutoAnalyze' threshold will help? I >> mean that any insert/update/delete statement that changes more then x% of >> table (and no less then y records) must do analyze right after it was >> finished. >> Defaults like x=50 y=10000 should be quite good as for me. > > If I am understanding things correctly, a full Analyze is going over all > the data in the table to figure out patterns. > > If this is the case, wouldn't it make sense in the situation where you are > loading an entire table from scratch to run the Analyze as you are > processing the data? If you don't want to slow down the main thread that's > inserting the data, you could copy the data to a second thread and do the > analysis while it's still in RAM rather than having to read it off of disk > afterwords. > > this doesn't make sense for updates to existing databases, but the use case > of loading a bunch of data and then querying it right away isn't _that_ > uncommon. > > David Lang > +1 for in-flight ANALYZE. This would be great for bulk loads of real tables as well as temp tables. Cheers, Ken