Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y3MiU-0001Eb-Mw for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Dec 2014 10:29:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y3MiT-0002sD-Lr for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Dec 2014 10:29:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y3MiS-0002s6-3c for pgsql-hackers@postgresql.org; Tue, 23 Dec 2014 10:29:24 +0000 Received: from smtp-outbound-1.vmware.com ([208.91.2.12]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y3MiK-0003Qn-Fn for pgsql-hackers@postgresql.org; Tue, 23 Dec 2014 10:29:20 +0000 Received: from sc9-mailhost1.vmware.com (sc9-mailhost1.vmware.com [10.113.161.71]) by smtp-outbound-1.vmware.com (Postfix) with ESMTP id CC07C287BB; Tue, 23 Dec 2014 02:29:10 -0800 (PST) Received: from EX13-CAS-012.vmware.com (EX13-CAS-012.vmware.com [10.113.191.64]) by sc9-mailhost1.vmware.com (Postfix) with ESMTP id C80ED19695; Tue, 23 Dec 2014 02:29:10 -0800 (PST) Received: from EX13-MBX-024.vmware.com (10.113.191.44) by EX13-MBX-008.vmware.com (10.113.191.28) with Microsoft SMTP Server (TLS) id 15.0.913.22; Tue, 23 Dec 2014 02:29:10 -0800 Received: from [192.168.1.90] (10.113.160.246) by EX13-MBX-024.vmware.com (10.113.191.44) with Microsoft SMTP Server (TLS) id 15.0.913.22; Tue, 23 Dec 2014 02:28:57 -0800 Message-ID: <549943DA.4090603@vmware.com> Date: Tue, 23 Dec 2014 12:28:42 +0200 From: Heikki Linnakangas Organization: VMware User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Icedove/31.3.0 MIME-Version: 1.0 To: Tomas Vondra , Subject: Re: PATCH: adaptive ndistinct estimator v3 (WAS: Re: [PERFORM] Yet another abort-early plan disaster on 9.3) References: <541A2335.3060100@agliodbs.com> <5429D517.3090900@agliodbs.com> <542C4E60.3030505@agliodbs.com> <542DADEB.2080103@agliodbs.com> <54381D20.9010501@fuzzy.cz> <5472416C.3080506@fuzzy.cz> <5483B346.6000203@fuzzy.cz> In-Reply-To: <5483B346.6000203@fuzzy.cz> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [10.113.160.246] X-ClientProxiedBy: EX13-CAS-013.vmware.com (10.113.191.65) To EX13-MBX-024.vmware.com (10.113.191.44) X-Pg-Spam-Score: -6.9 (------) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org On 12/07/2014 03:54 AM, Tomas Vondra wrote: > The one interesting case is the 'step skew' with statistics_target=10, > i.e. estimates based on mere 3000 rows. In that case, the adaptive > estimator significantly overestimates: > > values current adaptive > ------------------------------ > 106 99 107 > 106 8 6449190 > 1006 38 6449190 > 10006 327 42441 > > I don't know why I didn't get these errors in the previous runs, because > when I repeat the tests with the old patches I get similar results with > a 'good' result from time to time. Apparently I had a lucky day back > then :-/ > > I've been messing with the code for a few hours, and I haven't found any > significant error in the implementation, so it seems that the estimator > does not perform terribly well for very small samples (in this case it's > 3000 rows out of 10.000.000 (i.e. ~0.03%). The paper [1] gives an equation for an upper bound of the error of this GEE estimator. How do the above numbers compare with that bound? [1] http://ftp.cse.buffalo.edu/users/azhang/disc/disc01/cd1/out/papers/pods/towardsestimatimosur.pdf - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers