From: Kenneth Marshall <ktm@rice.edu>
To: Robert Haas <robertmhaas@gmail.com>
Cc: david@lang.hm, Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: Fri, 4 Feb 2011 08:52:20 -0600
Message-ID: <20110204145220.GC1261@aart.is.rice.edu> (raw)
In-Reply-To: <AANLkTikN14hsVVVLJ=BQdqEcpmzkLLqSbEpqx-th4Ohx@mail.gmail.com>
References: <AANLkTin7A_Q=Yc9Wf_5=POp2mVqmaiwqUDsFMLG68H45@mail.gmail.com>
<4D4A7B69.2020704@gmail.com>
<alpine.DEB.2.00.1102030208440.8162@asgard.lang.hm>
<AANLkTi=Thet0VZ-X6b7tek+iSLVkE27JjiKrB5D8U0Jd@mail.gmail.com>
<alpine.DEB.2.00.1102031243190.10088@asgard.lang.hm>
<AANLkTikmChsPODbUD4V-gQ8k2yYvvjJY6b9K_29+k7nu@mail.gmail.com>
<alpine.DEB.2.00.1102031637480.30983@asgard.lang.hm>
<AANLkTi=Kx6rRFs3Kz2m32nLKwLGv0Ee87UF8ngKhppgM@mail.gmail.com>
<alpine.DEB.2.00.1102031732470.30983@asgard.lang.hm>
<AANLkTikN14hsVVVLJ=BQdqEcpmzkLLqSbEpqx-th4Ohx@mail.gmail.com>
On Thu, Feb 03, 2011 at 09:05:52PM -0500, Robert Haas wrote:
> On Thu, Feb 3, 2011 at 8:37 PM, <david@lang.hm> wrote:
> > On Thu, 3 Feb 2011, Robert Haas wrote:
> >
> >> On Thu, Feb 3, 2011 at 7:39 PM, ?<david@lang.hm> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ktm@rice.edu, robertmhaas@gmail.com, tivv00@gmail.com, jnelson+pgsql@jamponi.net, mladen.gogala@vmsinfo.com, andrew@dunslane.net, bruce@momjian.us, tgl@sss.pgh.pa.us, craig@postnewspapers.com.au
Subject: Re: [HACKERS] Slow count(*) again...
In-Reply-To: <20110204145220.GC1261@aart.is.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox