Re: Regression: 8.3 2 seconds -> 8.4 100+ seconds

On Wed, Oct 27, 2010 at 8:41 AM, Francisco Reyes <lists(at)stringsutils(dot)com> wrote:
>                                ->  Nested Loop  (cost=293.80..719.87
> rows=2434522 width=4) (actual time=228.867..241.909 rows=2 loops=1)
>                                      ->  HashAggregate  (cost=293.80..294.13
> rows=33 width=29) (actual time=169.551..169.553 rows=2 loops=1)
>                                            ->  Nested Loop
>  (cost=11.33..293.71 rows=33 width=29) (actual time=145.940..169.543 rows=2
> loops=1)
>                                                  ->  HashAggregate
>  (cost=11.33..11.66 rows=33 width=4) (actual time=64.730..64.732 rows=2
> loops=1)
>                                                        ->  Index Scan using
> members_commonid on members  (cost=0.00..11.25 rows=33 width=4) (actual time
> = 64.688..64.703 rows=2 loops=1)
>                                                              Index Cond:
> (commonid = 3594)
>                                                  ->  Index Scan using
> cards_membid on cards  (cost=0.00..8.53 rows=1 width=33) (actual time=
> 52.400..52.401 rows=1 loops=2)
>                                                        Index Cond:
> (public.cards.membid = public.members.membid)
>                                      ->  Index Scan using cards_useraccount
> on cards  (cost=0.00..12.88 rows=2 width=33) (actual time=36.172..  36.173
> rows=1 loops=2)
>                                            Index Cond:
> (public.cards.useraccount = public.cards.useraccount)

This part looks really strange to me. Here we have a nested loop
whose outer side is estimated to produce 33 rows and whose outer side
is estimated to produce 2 rows. Given that, one would think that the
estimate for the loop as a whole shouldn't be more than 33 * 2 = 66
rows (or maybe a bit more if 33 is really 33.4999 rounded down, and 2
is really 2.49999 rounded down). But the actual estimate is 5 orders
of magnitude larger. How is that possible?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company