From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Cc: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: sum of left join greater than its parts
Date: Tue, 17 Jan 2006 13:09:53 -0800
Message-ID: <200601171309.53377.josh@agliodbs.com> (raw)
In-Reply-To: <1137517669.28011.728.camel@camel>
References: <1137517669.28011.728.camel@camel>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, xzilla@users.sourceforge.net
Subject: Re: sum of left join greater than its parts
In-Reply-To: <200601171309.53377.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox