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