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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to