Re: sum of left join greater than its parts

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
Subject: Re: sum of left join greater than its parts
Date: 2006-01-17 21:09:53
Message-ID: 200601171309.53377.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message mark 2006-01-17 21:12:59 Re: Suspending SELECTs
Previous Message Tom Lane 2006-01-17 21:01:33 Re: wildcard search performance with "like"