Why does row estimation on nested loop make no sense to me

From: Jeff Amiel <becauseimjeff(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Why does row estimation on nested loop make no sense to me
Date: 2013-05-17 16:25:28
Message-ID: 1368807928.97863.YahooMailNeo@web161406.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-05-17 16:35:22 Re: Why does row estimation on nested loop make no sense to me
Previous Message Amit Langote 2013-05-17 16:22:54 Re: Comunication protocol