From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | Jeff Amiel <becauseimjeff(at)yahoo(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Why does row estimation on nested loop make no sense to me |
Date: | 2013-05-17 16:37:49 |
Message-ID: | CA+HiwqHM5oT_uvRkhtwGvKyhN6_perH8SWTvhz+hpT2MUAN_6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, May 18, 2013 at 1:25 AM, Jeff Amiel <becauseimjeff(at)yahoo(dot)com> wrote:
> On most nested loops that I do explain/explain analyze on, the row estimation for the nested-loop itself is a product of the inner nodes of the nested loop.
> However in this case, I am stumped!
>
> explain
> select era.child_entity from entity_rel era join user_entity ue on ue.entity_id = era.parent_entity and ue.user_id=12345
>
> Nested Loop (cost=0.00..2903.37 rows=29107 width=4)
> -> Index Only Scan using entity_pk on user_entity ue (cost=0.00..62.68 rows=2 width=4)
> Index Cond: (user_id = 10954)
> -> Index Scan using rel_parent on entity_rel era (cost=0.00..1261.85 rows=317 width=8)
> Index Cond: (parent_entity = ue.entity_id)
>
>
> How can the estimated number of rows for the nested loop node EXCEED the product of the 2 row estimates of the tables being joined?
> Not only does it exceed it - but it is orders of magnitude greater.
>
> Am I missing something obvious here? I an see the nested loop row estimate being LESS but certainly not more.
>
Can you also post the output of explain analyze <your-query>?
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Amiel | 2013-05-17 16:42:09 | Re: Why does row estimation on nested loop make no sense to me |
Previous Message | Tom Lane | 2013-05-17 16:35:22 | Re: Why does row estimation on nested loop make no sense to me |