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

----- Original Message -----
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>
Sent: Friday, May 17, 2013 2:21 PM
Subject: Re: [GENERAL] Why does row estimation on nested loop make no sense to me

>> explain analyze
>> 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) (actual time=0.028..0.274 rows=201 loops=1)
>>  ->  Index Only Scan using entity_pk on user_entity ue  (cost=0.00..62.68 rows=2 width=4) (actual time=0.011..0.012 rows=1 loops=1)
>>        Index Cond: (user_id = 12345)
>>        Heap Fetches: 1
>>  ->  Index Scan using rel_parent on entity_rel era  (cost=0.00..1261.85 rows=317 width=8) (actual time=0.013..0.164 rows=201 loops=1)
>>        Index Cond: (parent_entity = ue.entity_id)
>> Total runtime: 0.361 ms

>I noticed when the explain output in your first mail shows Index Cond:
>(user_id = 10954) whereas your query says: ue.user_id=12345. Something
>with that? Although, your explain analyze does show the same values at
>both places with the row estimate being 29107 in both cases, which,
>well, looks awful and quite unexpected though there seem to have been
>similar observations before

That was a weak attempt at hiding 'real' data - intended to change them all to 12345.
:)

>Did you also check select count(*) on both the relations and found
>related numbers?

Nothing related (that I could find)  on the rowcounts - one table has 20 million rows or so ad the other 65K.