On Tue, Apr 30, 2013 at 7:20 AM, Christoph Berg <christoph.berg@xxxxxxxxxxx> wrote: > -> Nested Loop (cost=24.57..844.83 rows=62335 width=4) (actual time=0.109..0.633 rows=23 loops=1) > -> Bitmap Heap Scan on acl acl_2 (cost=8.90..61.36 rows=33 width=10) (actual time=0.070..0.112 rows=22 loops=1) > Recheck Cond: ((((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::System'::text)) OR (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::Queue'::text) AND (objectid = 10))) > -> BitmapOr (cost=8.90..8.90 rows=35 width=0) (actual time=0.064..0.064 rows=0 loops=1) > -> Bitmap Index Scan on acl1 (cost=0.00..4.47 rows=22 width=0) (actual time=0.036..0.036 rows=8 loops=1) > Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::System'::text)) > -> Bitmap Index Scan on acl1 (cost=0.00..4.41 rows=13 width=0) (actual time=0.026..0.026 rows=14 loops=1) > Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::Queue'::text) AND (objectid = 10)) > -> Bitmap Heap Scan on groups groups_3 (cost=15.67..23.73 rows=1 width=30) (actual time=0.022..0.023 rows=1 loops=22) > Recheck Cond: ((acl_2.principalid = id) OR ((((type)::text = (acl_2.principaltype)::text) AND (instance = 10) AND ((domain)::text = 'RT::Queue-Role'::text)) OR (((type)::text = (acl_2.principaltype)::text) AND (instance = 999028) AND ((domain)::text = 'RT::Ticket-Role'::text)))) > Filter: ((((domain)::text = 'SystemInternal'::text) OR ((domain)::text = 'UserDefined'::text) OR ((domain)::text = 'ACLEquivalence'::text) OR (((domain)::text = 'RT::Queue-Role'::text) AND (instance = 10)) OR (((domain)::text = 'RT::Ticket-Role'::text) AND (instance = 999028))) AND (((acl_2.principalid = id) AND ((acl_2.principaltype)::text = 'Group'::text) AND (((domain)::text = 'SystemInternal'::text) OR ((domain)::text = 'UserDefined'::text) OR ((domain)::text = 'ACLEquivalence'::text))) OR (((((domain)::text = 'RT::Queue-Role'::text) AND (instance = 10)) OR (((domain)::text = 'RT::Ticket-Role'::text) AND (instance = 999028))) AND ((type)::text = (acl_2.principaltype)::text)))) > -> BitmapOr (cost=15.67..15.67 rows=2 width=0) (actual time=0.019..0.019 rows=0 loops=22) > -> Bitmap Index Scan on groups_pkey (cost=0.00..4.76 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=22) > Index Cond: (acl_2.principalid = id) > -> BitmapOr (cost=10.66..10.66 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=22) > -> Bitmap Index Scan on groups2 (cost=0.00..5.33 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=22) > Index Cond: (((type)::text = (acl_2.principaltype)::text) AND (instance = 10) AND ((domain)::text = 'RT::Queue-Role'::text)) > -> Bitmap Index Scan on groups2 (cost=0.00..5.33 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=22) > Index Cond: (((type)::text = (acl_2.principaltype)::text) AND (instance = 999028) AND ((domain)::text = 'RT::Ticket-Role'::text)) The planner is estimating this the outer side of this nested loop will produce 33 rows and that the inner side will produce 1. One would assume that the row estimate for the join product couldn't be more than 33 * 1 = 33 rows, but the planner is estimating 62335 rows, which seems like nonsense. The actual result cardinality is 23. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
