----- Original Message -----
From: Amit Langote <amitlangote09@gmail.com>
To: Jeff Amiel <becauseimjeff@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.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=1loops=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=201loops=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.