Re: 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

>
>>Have you tried analyze (it's probably a case of insufficient/outdated
>>statistics to planner's disposal) or probably consider changing
>>default_statistics_target?
>
>
> Again - my question revolves not around the whether or not I am getting good or bad estimates - my question is related to the fact that the nested-loop row estimation does not appear to be derived from the nodes below it - it is off by orders of magnitude. I've never seen this before.
> That aside, yes - I did analyze and tweak stats target during experimentation - no change.

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

--
Amit Langote