From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Francisco Reyes <lists(at)stringsutils(dot)com> |
Cc: | Pgsql performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Regression: 8.3 2 seconds -> 8.4 100+ seconds |
Date: | 2010-11-07 01:23:28 |
Message-ID: | AANLkTi=VotmtMz5tYcnQ_5nDhZmsvp_9oYq43FF+qjnn@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Rostron | 2010-11-07 20:33:05 | questions regarding shared_buffers behavior |
Previous Message | Craig Ringer | 2010-11-07 00:21:18 | Re: Running PostgreSQL as fast as possible no matter the consequences |