X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1FF7752A6B; Tue, 3 May 2005 21:45:28 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45468-01; Wed, 4 May 2005 00:45:26 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 0749054225; Tue, 3 May 2005 21:45:23 -0300 (ADT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j440jMvF005033; (envelope-from ) Tue, 3 May 2005 19:45:22 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j440jILb006109 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Tue, 3 May 2005 19:45:19 -0500 (CDT) Message-ID: <42781B1D.7070101@arbash-meinel.com> Date: Tue, 03 May 2005 19:45:17 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324) X-Accept-Language: en-us, en MIME-Version: 1.0 To: josh@agliodbs.com Cc: Mischa Sandberg , Markus Schaber , pgsql-perform , pgsql-hackers@postgresql.org Subject: Re: [PERFORM] Bad n_distinct estimation; hacks suggested? References: <200504191209.05181.josh@agliodbs.com> <4277774F.7040205@logix-tt.com> <1115155990.4277ee16aba34@webmail.telus.net> <200505031443.44859.josh@agliodbs.com> In-Reply-To: <200505031443.44859.josh@agliodbs.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig218E20F1D20FF38DF2FF840B" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/301 X-Sequence-Number: 67177 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig218E20F1D20FF38DF2FF840B Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Josh Berkus wrote: > Mischa, > > >>Okay, although given the track record of page-based sampling for >>n-distinct, it's a bit like looking for your keys under the streetlight, >>rather than in the alley where you dropped them :-) > > > Bad analogy, but funny. > > The issue with page-based vs. pure random sampling is that to do, for example, > 10% of rows purely randomly would actually mean loading 50% of pages. With > 20% of rows, you might as well scan the whole table. > > Unless, of course, we use indexes for sampling, which seems like a *really > good* idea to me .... > But doesn't an index only sample one column at a time, whereas with page-based sampling, you can sample all of the columns at once. And not all columns would have indexes, though it could be assumed that if a column doesn't have an index, then it doesn't matter as much for calculations such as n_distinct. But if you had 5 indexed rows in your table, then doing it index wise means you would have to make 5 passes instead of just one. Though I agree that page-based sampling is important for performance reasons. John =:-> --------------enig218E20F1D20FF38DF2FF840B Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFCeBsdJdeBCYSNAAMRAu9UAKDDdD+Gc/SgZXjTo15SPIUtklrYZgCfd3VR 4o/cdrYTBR9vdo3fekGjZns= =dsMV -----END PGP SIGNATURE----- --------------enig218E20F1D20FF38DF2FF840B--