Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> On Fri, Nov 19, 2010 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hard to comment about this with such an incomplete view of the situation
>> --- in particular, data types would be a critical factor, and I also
>> wonder if you're admitting to all the columns involved.
> Here is an example that, while super ugly, does show the problem:
Hm. In the UNION case, we have a measured average width for the inet
column from ANALYZE for each table, and we just use that. In the
sub-select case, it seems to be falling back to a default estimate for
the datatype, which surprises me a bit --- at least in HEAD it seems
like it should be smarter. I'll go look at that.
As for the rowcount estimates, these aren't the same query so there's no
reason for them to be the same. In the UNION case, it's basically
taking the pessimistic assumption that there are no duplicate rows;
given the lack of cross-column stats there's no way to be much smarter.
In the GROUP BY case, the question is how many distinct values of 'a'
you suppose there are. This is also pretty hard to be rigorous about
--- we have an idea of that for each table, but no idea how many
cross-table duplications there are. The 200 is just a default estimate
when it has no idea. We could probably come up with some better though
not rigorous estimate, but nobody's worked on it.
regards, tom lane