Re: sum of left join greater than its parts

[Josh Berkus <josh(at)agliodbs(dot)com>]

Hmmm, this looks like a planner bug to me:

> Hash
> Join  (cost=870.00..992.56 rows=1 width=96) (actual time=90.566..125.782
> rows=472 loops=1) Hash Cond: (("outer".host_id = "inner".host_id) AND
> ("outer"."?column2?" = "inner".mtime)) ->  HashAggregate 
> (cost=475.88..495.32 rows=1555 width=16) (actual time=51.300..70.761
> rows=10870 loops=1)

>-- Nested Loop  (cost=1733.79..4620.38 rows=1 width=20) (actual
> time=81.160..89.826 rows=238 loops=1) ->  Nested Loop 
> (cost=1733.79..4615.92 rows=1 width=20) (actual time=81.142..86.826
> rows=238 loops=1) Join Filter: ("outer".rmsbinaryid =
> "inner".rmsbinaryid) ->  HashAggregate  (cost=1733.79..1740.92 rows=570
> width=12) (actual time=81.105..81.839 rows=323 loops=1) ->  Bitmap Heap
> Scan on msg306u  (cost=111.75..1540.65 rows=25752 width=12) (actual
> time=4.490..41.233 rows=25542 loops=1)

Notice that for both queries, the estimates are reasonably accurate (within 
+/- 4x) until they get to left joining the subquery, at which point the 
estimate of rows joined becomes exactly "1".   That looks suspicios to 
me ... Tom?  Neil?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco