Bug 1005056

I wasn't able to make progress on this. Someone else can pick it up if they want, here are my findings:

Current query:
  select scp.package_id,
         pn.id || '|' || pe.id || '|' || pa.id as id_combo,
         pn.name || '-' || pe.version || '-' || pe.release as nvre,
         pn.name || '-' || evr_t_as_vre_simple(pe.evr) || '.' || pa.label as nvrea,
         pn.name as name,
         pe.version as version,
         pe.release as release,
         pe.epoch as epoch,
         pn.id as name_id,
         pe.id as evr_id,
         pa.label as arch,
         sp.installTime
    from rhnServerPackage sp
         left outer join (select sc.server_id,
                                 cp.package_id,
                                 p.name_id,
                                 p.evr_id,
                                 p.package_arch_id
                            from rhnPackage p,
                                 rhnServerChannel sc
                            join rhnChannelPackage cp
                              on (sc.channel_id = cp.channel_id)
                           where cp.package_id = p.id
                         ) scp on (scp.server_id = sp.server_id and
                                   sp.name_id = scp.name_id and
                                   sp.evr_id = scp.evr_id and
                                   sp.package_arch_id = scp.package_arch_id)
         left outer join rhnPackageName pn on (pn.id = sp.name_id)
         left outer join rhnPackageArch pa on (pa.id = sp.package_arch_id)
         left outer join rhnPackageEvr pe on (pe.id = sp.evr_id)
   where sp.server_id = :serverid and
         (scp.package_id is null)
   order by nvre;

Current explain plan (on Postgres 8):

 Sort  (cost=4233.92..4233.93 rows=1 width=273)
   Sort Key: ((((((pn.name)::text || '-'::text) || (pe.version)::text) || '-'::text) || (pe.release)::text))
   ->  Nested Loop Left Join  (cost=3253.53..4233.91 rows=1 width=273)
         ->  Nested Loop Left Join  (cost=3253.53..4225.34 rows=1 width=258)
               ->  Nested Loop Left Join  (cost=3253.53..4217.06 rows=1 width=87)
                     ->  Nested Loop Left Join  (cost=3253.53..4208.78 rows=1 width=37)
                           Join Filter: ((sc.server_id = sp.server_id) AND (sp.name_id = p.name_id) AND (sp.evr_i
d = p.evr_id) AND (sp.package_arch_id = p.package_arch_id))
                           Filter: (cp.package_id IS NULL)
                           ->  Index Scan using rhn_sp_snep_uq on rhnserverpackage sp  (cost=0.00..8.28 rows=1 wi
dth=39)
                                 Index Cond: (server_id = 1000010052::numeric)
                           ->  Hash Join  (cost=3253.53..3910.52 rows=14499 width=39)
                                 Hash Cond: (cp.package_id = p.id)
                                 ->  Nested Loop  (cost=193.39..551.29 rows=15410 width=18)
                                       ->  Seq Scan on rhnserverchannel sc  (cost=0.00..1.64 rows=1 width=17)
                                             Filter: (server_id = 1000010052::numeric)
                                       ->  Bitmap Heap Scan on rhnchannelpackage cp  (cost=193.39..463.95 rows=68
56 width=15)
                                             Recheck Cond: (cp.channel_id = sc.channel_id)
                                             ->  Bitmap Index Scan on rhn_cp_cp_uq  (cost=0.00..191.68 rows=6856 
width=0)
                                                   Index Cond: (cp.channel_id = sc.channel_id)
                                 ->  Hash  (cost=2737.84..2737.84 rows=25784 width=29)
                                       ->  Seq Scan on rhnpackage p  (cost=0.00..2737.84 rows=25784 width=29)
                     ->  Index Scan using rhn_pe_id_pk on rhnpackageevr pe  (cost=0.00..8.27 rows=1 width=57)
                           Index Cond: (pe.id = sp.evr_id)
               ->  Index Scan using rhn_parch_id_pk on rhnpackagearch pa  (cost=0.00..8.27 rows=1 width=178)
                     Index Cond: (pa.id = sp.package_arch_id)
         ->  Index Scan using rhn_pn_id_pk on rhnpackagename pn  (cost=0.00..8.27 rows=1 width=22)
               Index Cond: (pn.id = sp.name_id) 


I tried inserting a "and sc.server_id = :serverid into the sub-select to see if that would make a difference, but it did not. The time to execute and the explain plan were both the same as with above.

Next I tried converting the left join on the sub-query to a not exists statement, even though the left join is generally faster. It was a little faster, but not a lot.

New query:
  select null as package_id,
         pn.id || '|' || pe.id || '|' || pa.id as id_combo,
         pn.name || '-' || pe.version || '-' || pe.release as nvre,
         pn.name || '-' || evr_t_as_vre_simple(pe.evr) || '.' || pa.label as nvrea,
         pn.name as name,
         pe.version as version,
         pe.release as release,
         pe.epoch as epoch,
         pn.id as name_id,
         pe.id as evr_id,
         pa.label as arch,
         sp.installTime
    from rhnServerPackage sp
         left outer join rhnPackageName pn on (pn.id = sp.name_id)
         left outer join rhnPackageArch pa on (pa.id = sp.package_arch_id)
         left outer join rhnPackageEvr pe on (pe.id = sp.evr_id)
   where sp.server_id = 1000010052
     and not exists (select 1
                       from rhnPackage p,
                            rhnServerChannel sc,
                            rhnChannelPackage cp
                      where cp.package_id = p.id
                        and sc.channel_id = cp.channel_id
                        and sc.server_id = sp.server_id
                        and p.name_id = sp.name_id
                        and p.evr_id = sp.evr_id
                        and p.package_arch_id = sp.package_arch_id)
   order by nvre;

New explain plan:

 Sort  (cost=4233.92..4233.93 rows=1 width=265)
   Sort Key: ((((((pn.name)::text || '-'::text) || (pe.version)::text) || '-'::text) || (pe.release)::text))
   ->  Nested Loop Left Join  (cost=3253.53..4233.91 rows=1 width=265)
         ->  Nested Loop Left Join  (cost=3253.53..4225.34 rows=1 width=250)
               ->  Nested Loop Left Join  (cost=3253.53..4217.06 rows=1 width=79)
                     ->  Nested Loop Anti Join  (cost=3253.53..4208.78 rows=1 width=29)
                           Join Filter: ((sc.server_id = sp.server_id) AND (p.name_id = sp.name_id) AND (p.evr_id
 = sp.evr_id) AND (p.package_arch_id = sp.package_arch_id))
                           ->  Index Scan using rhn_sp_snep_uq on rhnserverpackage sp  (cost=0.00..8.28 rows=1 wi
dth=39)
                                 Index Cond: (server_id = 1000010052::numeric)
                           ->  Hash Join  (cost=3253.53..3910.52 rows=14499 width=31)
                                 Hash Cond: (cp.package_id = p.id)
                                 ->  Nested Loop  (cost=193.39..551.29 rows=15410 width=18)
                                       ->  Seq Scan on rhnserverchannel sc  (cost=0.00..1.64 rows=1 width=17)
                                             Filter: (server_id = 1000010052::numeric)
                                       ->  Bitmap Heap Scan on rhnchannelpackage cp  (cost=193.39..463.95 rows=68
56 width=15)
                                             Recheck Cond: (cp.channel_id = sc.channel_id)
                                             ->  Bitmap Index Scan on rhn_cp_cp_uq  (cost=0.00..191.68 rows=6856 
width=0)
                                                   Index Cond: (cp.channel_id = sc.channel_id)
                                 ->  Hash  (cost=2737.84..2737.84 rows=25784 width=29)
                                       ->  Seq Scan on rhnpackage p  (cost=0.00..2737.84 rows=25784 width=29)
                     ->  Index Scan using rhn_pe_id_pk on rhnpackageevr pe  (cost=0.00..8.27 rows=1 width=57)
                           Index Cond: (pe.id = sp.evr_id)
               ->  Index Scan using rhn_parch_id_pk on rhnpackagearch pa  (cost=0.00..8.27 rows=1 width=178)
                     Index Cond: (pa.id = sp.package_arch_id)
         ->  Index Scan using rhn_pn_id_pk on rhnpackagename pn  (cost=0.00..8.27 rows=1 width=22)
               Index Cond: (pn.id = sp.name_id)

As you can see it's not a lot different, and we still do a sequential scan on rhnpackage, which is where most of the pain comes from.

Not quite sure where to go from here.

With the release of Red Hat Satellite 5.7 on January 12th 2015 this bug is
being moved to a Closed Current Release state. 

The Satellite 5.7 GA Errata:
 - https://rhn.redhat.com/errata/RHSA-2015-0033.html 

Satellite 5.7 Release Notes:
 -
https://access.redhat.com/documentation/en-US/Red_Hat_Satellite/5.7/html-single/Release_Notes/index.html

Satellite Customer Portal Blog announcement for release:
 - https://access.redhat.com/blogs/1169563/posts/1315743 

Cliff