Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id A66391337B95 for ; Fri, 4 Feb 2011 10:52:30 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 98142-01 for ; Fri, 4 Feb 2011 14:52:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh7.mail.rice.edu (mh7.mail.rice.edu [128.42.199.46]) by mail.postgresql.org (Postfix) with ESMTP id 1A0521337B8D for ; Fri, 4 Feb 2011 10:52:20 -0400 (AST) Received: from mh7.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh7.mail.rice.edu (Postfix) with ESMTP id 99D0E28F941; Fri, 4 Feb 2011 08:52:21 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh7.mail.rice.edu, auth channel Received: from mh7.mail.rice.edu ([127.0.0.1]) by mh7.mail.rice.edu (mh7.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id 8VBISrzc-7zf; Fri, 4 Feb 2011 08:52:21 -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 mh7.mail.rice.edu (Postfix) with ESMTP id 50B1D28F928; Fri, 4 Feb 2011 08:52:21 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id CA9352D5A6; Fri, 4 Feb 2011 08:52:20 -0600 (CST) Date: Fri, 4 Feb 2011 08:52:20 -0600 From: Kenneth Marshall To: Robert Haas Cc: david@lang.hm, Vitalii Tymchyshyn , Jon Nelson , Mladen Gogala , Andrew Dunstan , Bruce Momjian , Tom Lane , Craig Ringer , "pgsql-performance@postgresql.org" Subject: Re: [HACKERS] Slow count(*) again... Message-ID: <20110204145220.GC1261@aart.is.rice.edu> References: <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=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/189 X-Sequence-Number: 42327 On Thu, Feb 03, 2011 at 09:05:52PM -0500, Robert Haas wrote: > On Thu, Feb 3, 2011 at 8:37 PM, wrote: > > On Thu, 3 Feb 2011, Robert Haas wrote: > > > >> On Thu, Feb 3, 2011 at 7:39 PM, ? wrote: > >>>> > >>>> Yeah, but you'll be passing the entire table through this separate > >>>> process that may only need to see 1% of it or less on a large table. > >>>> If you want to write the code and prove it's better than what we have > >>>> now, or some other approach that someone else may implement in the > >>>> meantime, hey, this is an open source project, and I like improvements > >>>> as much as the next guy. ?But my prediction for what it's worth is > >>>> that the results will suck. ?:-) > >>> > >>> I will point out that 1% of a very large table can still be a lot of disk > >>> I/O that is avoided (especially if it's random I/O that's avoided) > >> > >> Sure, but I think that trying to avoid it will be costly in other ways > >> - you'll be streaming a huge volume of data through some auxiliary > >> process, which will have to apply some algorithm that's very different > >> from the one we use today. ?The reality is that I think there's little > >> evidence that the way we do ANALYZE now is too expensive. ?It's > >> typically very cheap and works very well. ?It's a bit annoying when it > >> fires off in the middle of a giant data load, so we might need to > >> change the time of it a little, but if there's a problem with the > >> operation itself being too costly, this is the first I'm hearing of > >> it. ?We've actually worked *really* hard to make it cheap. > > > > I could be misunderstanding things here, but my understanding is that it's > > 'cheap' in that it has little impact on the database while it is running. > > I mean that it's cheap in that it usually takes very little time to complete. > > > the issue here is that the workflow is > > > > load data > > analyze > > start work > > > > so the cost of analyze in this workflow is not "1% impact on query speed for > > the next X time", it's "the database can't be used for the next X time while > > we wait for analyze to finish running" > > OK. > > > I don't understand why the algorithm would have to be so different than > > what's done today, surely the analyze thread could easily be tweaked to > > ignore the rest of the data (assuming we don't have the thread sending the > > data to analyze do the filtering) > > If you want to randomly pick 10,000 rows out of all the rows that are > going to be inserted in the table without knowing in advance how many > there will be, how do you do that? Maybe there's an algorithm, but > it's not obvious to me. But mostly, I question how expensive it is to > have a second process looking at the entire table contents vs. going > back and rereading a sample of rows at the end. I can't remember > anyone ever complaining "ANALYZE took too long to run". I only > remember complaints of the form "I had to remember to manually run it > and I wish it had just happened by itself". > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company Probably doomed to be shot down, but since you are effectively inline, you could sample assuming a range of table row counts. Start at the size of a table where random (index) lookups are faster than a sequential scan and then at appropriate multiples, 100x, 100*100X,... then you should be able to generate appropriate statistics. I have not actually looked at how that would happen, but it would certainly allow you to process far, far fewer rows than the entire table. Regards, Ken