----- Original Message ----- From: Amit Langote <amitlangote09@xxxxxxxxx> To: Jeff Amiel <becauseimjeff@xxxxxxxxx> Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> Sent: Friday, May 17, 2013 2:21 PM Subject: 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 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
