Re: RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1

On Tue, Apr 30, 2013 at 7:20 AM, Christoph Berg
<christoph.berg@credativ.de> wrote:
>                                                    ->  Nested Loop  (cost=24.57..844.83 rows=62335 width=4) (actual
time=0.109..0.633rows=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)
(actualtime=0.064..0.064 rows=0 loops=1) 
>                                                                      ->  Bitmap Index Scan on acl1  (cost=0.00..4.47
rows=22width=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=13width=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=1width=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)
(actualtime=0.019..0.019 rows=0 loops=22) 
>                                                                      ->  Bitmap Index Scan on groups_pkey
(cost=0.00..4.76rows=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)
(actualtime=0.013..0.013 rows=0 loops=22) 
>                                                                            ->  Bitmap Index Scan on groups2
(cost=0.00..5.33rows=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.33rows=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