Re: BUG #7619: Query cost estimate appears to not use n_distinct setting

On 10/23/2012 06:46 PM, niko(dot)kiirala(at)mapvision(dot)fi wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7619
> Logged by: Niko Kiirala
> Email address: niko(dot)kiirala(at)mapvision(dot)fi
> PostgreSQL version: 9.2.1
> Operating system: Windows 7 SP 1 (64-bit)
> Description:
>
> I am working on a potentially large database table, let's call it
> "observation", that has a foreign key to table "measurement". Each
> measurement is associated with either none or around five observations. In
> this kind of situation, it is well known that the statistics on the foreign
> key column in observation table can get arbitrarily bad as the row count
> increases. Especially, the estimate of the number of distinct values in the
> foreign key column can be completely off.

For anyone wondering why this feels familiar, the same message was
posted to pgsql-performance earlier:

http://postgresql.1045698.n5.nabble.com/High-cost-estimates-when-n-distinct-is-set-td5728596.html

It's clear that there's a practical performance issue here, but less
clear that it's a bug. Nonetheless, thanks for writing it up in so much
detail and chasing it up further - though it'd be nice if you'd
mentioned your earlier post.

I'd love to help, but you've clearly already done a lot of work on this
and I'm not sure I have anything useful to add.

If you don't have any luck, consider asking one of the professional
PostgreSQL consulting firms for their input.

--
Craig Ringer