On 10/23/2012 06:46 PM, niko.kiirala@mapvision.fi wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7619
> Logged by: Niko Kiirala
> Email address: niko.kiirala@mapvision.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