Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2B8E2635535 for ; Tue, 24 Feb 2009 13:09:04 -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 46541-01-3 for ; Tue, 24 Feb 2009 13:08:58 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ringonce.com (zalloo.com [62.213.196.40]) by mail.postgresql.org (Postfix) with ESMTP id 309B56351D5 for ; Tue, 24 Feb 2009 13:08:57 -0400 (AST) Received: from mail.securax.org (unknown [212.123.25.145]) by ringonce.com (Postfix) with ESMTPS id A820310C87FF for ; Tue, 24 Feb 2009 18:08:53 +0100 (CET) Received: (qmail 14455 invoked by uid 2077); 24 Feb 2009 16:11:17 -0000 Received: from unknown (HELO rt.attractel.com) (89.253.129.130) by mail.securax.org with (DHE-RSA-AES256-SHA encrypted) SMTP; 24 Feb 2009 16:11:17 -0000 Received: (qmail 18032 invoked by uid 89); 24 Feb 2009 17:08:52 -0000 Received: by simscan 1.4.0 ppid: 18026, pid: 18029, t: 0.0729s scanners: clamav: 0.92.1 /m: Received: from unknown (HELO ?10.2.2.158?) (kouber@securax.org@10.2.2.158) by cocaine with ESMTPA; 24 Feb 2009 17:08:52 -0000 Message-ID: <49A429A3.9010803@saparev.com> Date: Tue, 24 Feb 2009 19:08:51 +0200 From: Kouber Saparev Reply-To: kouber@saparev.com User-Agent: Thunderbird 2.0.0.19 (Windows/20081209) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: LIMIT confuses the planner References: <49A295F0.5010405@saparev.com> <49A2A450.20002@archonet.com> <49A2DFFA.8000001@saparev.com> <18725.1235412110@sss.pgh.pa.us> In-Reply-To: <18725.1235412110@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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: 200902/293 X-Sequence-Number: 32874 Tom Lane wrote: > Kouber Saparev writes: >> Now the planner believes there're 910 rows, which is a bit closer to the >> real data: > >> swing=# select avg(length) from (select username, count(*) as length >> from login_attempt group by username) as freq; >> avg >> ---------------------- >> 491.6087310427555479 >> (1 row) > > Hmph, that's still not real good. Ideally it should be estimating > *less* than the average frequency, because the estimate is made after > excluding all the most-common-values, which evidently 'kouber' is not > one of. I suppose there's quite a large number of infrequently-seen > usernames and the ndistinct estimate is much less than reality? (Look > at the pg_stats row for this column.) It might be worth going all the > way to stats target 1000 for this column. I altered the statistics for that column to 1000, so now the planner assumes exactly 492 rows for the fore-mentioned query, which is indeed the average. It never went *less* than that value, it was always higher, i.e. for a statistics value of 600, it was 588, for 800, it became 540. The current value of n_distinct (given statistics=1000) is: db=# SELECT n_distinct FROM pg_stats WHERE tablename='login_attempt' AND attname='username'; n_distinct ------------ 5605 (1 row) db=# SELECT COUNT(DISTINCT username) FROM login_attempt; count ------- 23391 (1 row) In fact, what is n_distinct standing for, apart the famous formula: n*d / (n - f1 + f1*n/N) ;-) Regards, -- Kouber Saparev http://kouber.saparev.com