Re: [HACKERS] Bad n_distinct estimation; hacks suggested? - Mailing list pgsql-performance

From Andrew Dunstan
Subject Re: [HACKERS] Bad n_distinct estimation; hacks suggested?
Date
Msg-id 1963.24.211.165.134.1114299868.squirrel@www.dunslane.net
Whole thread Raw
In response to Re: Bad n_distinct estimation; hacks suggested?  (Josh Berkus <josh@agliodbs.com>)
List pgsql-performance
Josh Berkus said:
>
>
> Well, unusual distributions are certainly tough.  But I think the
> problem  exists even for relatively well-distributed populations.
> Part of it is, I  believe, the formula we are using:
>
> n*d / (n - f1 + f1*n/N)
>
[snip]
>
> This is so broken, in fact, that I'm wondering if we've read the paper
> right?   I've perused the paper on almaden, and the DUJ1 formula
> appears considerably  more complex than the formula we're using.
>
> Can someone whose math is more recent than calculus in 1989 take a look
> at  that paper, and look at the formula toward the bottom of page 10,
> and see if  we are correctly interpreting it?    I'm particularly
> confused as to what "q"  and "d-sub-n" represent.  Thanks!
>

Math not too recent ...

I quickly read the paper and independently came up with the same formula you
say above we are applying. The formula is on the page that is numbered 6,
although it's the tenth page in the PDF.

q = n/N  = ratio of sample size to population size
d_sub_n = d = number of distinct classes in sample

cheers

andrew





pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: two queries and dual cpu (perplexed)
Next
From: Thomas F.O'Connell
Date:
Subject: Re: pgbench Comparison of 7.4.7 to 8.0.2