Jeff Davis <pgsql@xxxxxxxxxxx> writes: > On Thu, 2008-08-28 at 00:42 -0400, Tom Lane wrote: >> The reason that these statements are not inconsistent is that the >> Sort is the inner relation for a mergejoin. In the presence of >> duplicate keys in the outer relation, a mergejoin will "rewind" and >> rescan duplicate keys in the inner relation; ... > Then wouldn't the planner have estimated more rows returned by the sort > (including rescanned rows) than the HashAgg? It estimated exactly the > same number as it estimated for the output of the HashAgg. No, the planner's numbers are correct for its purposes --- what it wants to know is the total footprint of each sub-relation, so as to estimate for instance the amount of space that'd be needed to hash or sort it. (It does in fact internally make an estimate of the number of repeatedly fetched rows, but this isn't reflected in the EXPLAIN output.) If anything this discrepancy is an implementation flaw in EXPLAIN ANALYZE: what it's measuring is not the number of tuples in the sub-relation, but the number of times a tuple is returned. There are some other cases, such as underneath a LIMIT, where what EXPLAIN ANALYZE will report is quite at variance with what the planner reports. regards, tom lane
