Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 10EC765038F for ; Fri, 12 Dec 2008 14:33:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 27639-01 for ; Fri, 12 Dec 2008 14:32:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail128168.authsmtp.com (outmail128168.authsmtp.com [62.13.128.168]) by mail.postgresql.org (Postfix) with ESMTP id 426B865038D for ; Fri, 12 Dec 2008 14:32:59 -0400 (AST) Received: from mail-c188.authsmtp.com (mail-c188.authsmtp.com [62.13.128.25]) by punt3.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id mBCIWFGL016247; Fri, 12 Dec 2008 18:32:15 GMT Received: from [192.168.0.3] (85-211-226-82.dyn.gotadsl.co.uk [85.211.226.82]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id mBCIWEYB092316; Fri, 12 Dec 2008 18:32:14 GMT Subject: Re: benchmarking the query planner From: Simon Riggs To: Tom Lane Cc: Gregory Stark , Robert Haas , Kevin Grittner , "jd@commandprompt.com" , Josh Berkus , Greg Smith , pgsql-hackers@postgresql.org In-Reply-To: <8020.1229105929@sss.pgh.pa.us> References: <603c8f070812061019r5aca3aajeaa5493b11c5feff@mail.gmail.com> <603c8f070812092025k6325e0fcud001b205a507a634@mail.gmail.com> <4313.1228931932@sss.pgh.pa.us> <603c8f070812101828p1bd7df49yb8c916adedb49fe1@mail.gmail.com> <9565.1229018968@sss.pgh.pa.us> <1229033073.13078.179.camel@hp_dx2400_1> <87ej0etkfh.fsf@oxford.xeocode.com> <1229039044.13078.193.camel@hp_dx2400_1> <4136ffa0812111823u645b6ec9wdca60b3da4b00499@mail.gmail.com> <1229072675.13078.196.camel@hp_dx2400_1> <603c8f070812120344m67ef2c1fs41806cfb4ff9e396@mail.gmail.com> <1229093975.13078.275.camel@ebony.2ndQuadrant> <87tz99s7bu.fsf@oxford.xeocode.com> <1229100057.8673.29.camel@ebony.2ndQuadrant> <8020.1229105929@sss.pgh.pa.us> Content-Type: text/plain Date: Fri, 12 Dec 2008 18:31:57 +0000 Message-Id: <1229106717.8673.88.camel@ebony.2ndQuadrant> Mime-Version: 1.0 X-Mailer: Evolution 2.12.0 Content-Transfer-Encoding: 7bit X-Server-Quench: 32c146c2-c87b-11dd-aecc-001871e930f4 X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksafhtFaQdbaVxS HGQLW1xEUVV7WmF/ ag4fZQBDYEtPQQdo VlZBQE1QEQdtHhxP W2xtK34HHndGcXxz ZEAsXHJYWwp5dxV8 RkgHF3BVbWAydWEe BBRFJlYHIx5Kfh5N bVEqXXANN2FJBC9q VzwTFhsSEA9kHWxf TwcGLVcbW0UKGDF0 WxAPVSwyEFcUDz03 JhogJ0R0 X-Authentic-SMTP: 61633235383639.cat.dmpriest.net.uk:1378/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/983 X-Sequence-Number: 129612 On Fri, 2008-12-12 at 13:18 -0500, Tom Lane wrote: > I seem to recall Greg suggesting that there were ways to estimate > ndistinct without sorting, but short of a fundamental algorithm change > there's not going to be a win here. Hash table? Haas Stokes suggests a Bloom filter. Why not keep the random algorithm we have now, but scan the block into a separate hash table for ndistinct estimation. That way we keep the correct random rows for other purposes. > > Right now we may as well use a random number generator. > > Could we skip the hyperbole please? Some of the ndistinct values are very badly off, and in the common cases I cited previously, consistently so. Once I'm certain the rescue helicopter has seen me, I'll stop waving my arms. (But yes, OK). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support