From: Kouber Saparev <kouber@saparev.com>
To: pgsql-performance@postgresql.org
Subject: Re: LIMIT confuses the planner
Date: Tue, 24 Feb 2009 19:08:51 +0200
Message-ID: <49A429A3.9010803@saparev.com> (raw)
In-Reply-To: <18725.1235412110@sss.pgh.pa.us>
References: <49A295F0.5010405@saparev.com>
<49A2A450.20002@archonet.com>
<49A2DFFA.8000001@saparev.com>
<18725.1235412110@sss.pgh.pa.us>
Tom Lane wrote:
> Kouber Saparev <kouber@saparev.com> 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
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: kouber@saparev.com
Subject: Re: LIMIT confuses the planner
In-Reply-To: <49A429A3.9010803@saparev.com>
* 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