Bug 1119447

Description of problem:

Click on a system profile's Software > Packages > List/Remove tab. For a system with ~1,100 installed packages, subscribed to a channel with ~12,000 packages, the page takes ~120 seconds to load. Any navigation of the list (clicking forward/backward, filtering on package name, clicking on the alphanumeric index) also takes the same amount of time to load.

Version-Release number of selected component (if applicable):

Reproduced on Satellite 5.6 w/ spacewalk-java-2.0.2-79.el6sat.noarch. Relevant code is the same in spacewalk (see below).


How reproducible:

100%

Steps to Reproduce:
1. See description section above.

Actual results:

Slow page loads, inability to click on managed packages if the system isn't currently subscribed to the channel providing the package.

Expected results:

Fast page loads, package clickable if managed by Satellite and visible to user.

Additional info:

Both the slow query execution and the inability to click certain packages are due to the changes to system_package_list in ./java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml that were introduced in:

spacewalk commit: c94bd801db0357703eacecf3a3066242a2a51913
satellite commit: cb1e9274ff8bb366470daa742ebb585809d0f788
Author: Tomas Lestach <tlestach>
Date:   Fri Aug 16 16:41:56 2013 +0200

    997809 - make unavailable packages non-clickable

<mode name="system_package_list" class="com.redhat.rhn.frontend.dto.PackageListItem">
  <query params="sid">
  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 = :sid
   ORDER BY nvre
  </query>
</mode>


The first left join looks only at rhnPackage records that are available in the system's currently-subscribed channels. If the package was installed from a child channel, but then the child channel was later unsubscribed, then no rhnPackage id will be returned, and the package in the list will be unclickable. This left join is also the reason for the slow page load. Explain output from a Satellite 5.6 (spacewalk-java-2.0.2-79.el6sat.noarch / satellite-schema-5.6.0.18-1.el6sat.noarch / spacewalk-schema-2.0.2-13.el6sat.noarch) with embedded postgres db:

                                                                                     QUERY PLAN     
-------------------------------------------------------------------------------- Sort  (cost=13466.85..13466.86 rows=1 width=113) (actual time=130823.326..130823.455 rows=1138 loops=1)
   Sort Key: ((((((pn.name)::text || '-'::text) || (pe.version)::text) || '-'::text) || (pe.release)::text))
   Sort Method:  quicksort  Memory: 349kB
   ->  Nested Loop Left Join  (cost=10401.89..13466.84 rows=1 width=113) (actual time=113.076..130814.365 rows=1138 loops=1)
         ->  Nested Loop Left Join  (cost=10401.89..13458.26 rows=1 width=98) (actual time=111.725..130728.096 rows=1138 loops=1)
               Join Filter: (pa.id = sp.package_arch_id)
               ->  Nested Loop Left Join  (cost=10401.89..13456.25 rows=1 width=88) (actual time=111.687..130693.650 rows=1138 loops=1)
                     ->  Nested Loop Left Join  (cost=10401.89..13447.97 rows=1 width=37) (actual time=111.645..130666.701 rows=1138 loops=1)
                           Join Filter: ((sc.server_id = sp.server_id) AND (sp.name_id = p.name_id) AND (sp.evr_id = p.evr_id) AND (sp.package_arch_id = p.package_arch_id))
                           ->  Index Scan using rhn_sp_snep_uq on rhnserverpackage sp  (cost=0.00..8.28 rows=1 width=40) (actual time=0.037..7.609 rows=1138 loops=1)
                                 Index Cond: (server_id = 1000010207::numeric)
                           ->  Hash Join  (cost=10401.89..13276.49 rows=8160 width=40) (actual time=78.654..109.324 rows=12602 loops=1138)
                                 Hash Cond: (cp.package_id = p.id)
                                 ->  Nested Loop  (cost=160.03..2208.64 rows=8160 width=19) (actual time=2.354..7.724 rows=12602 loops=1138)
                                       ->  Index Scan using rhn_sc_sid_cid_uq on rhnserverchannel sc  (cost=0.00..8.27 rows=1 width=18) (actual time=0.017..0.018 rows=1 loops=1138)
                                             Index Cond: (server_id = 1000010207::numeric)
                                       ->  Bitmap Heap Scan on rhnchannelpackage cp  (cost=160.03..2136.29 rows=5126 width=15) (actual time=2.322..4.515 rows=12602 loops=1138)
                                             Recheck Cond: (cp.channel_id = sc.channel_id)
                                             ->  Bitmap Index Scan on rhn_cp_cp_uq  (cost=0.00..158.75 rows=5126 width=0) (actual time=2.289..2.289 rows=12602 loops=1138)
                                                   Index Cond: (cp.channel_id = sc.channel_id)
                                 ->  Hash  (cost=8610.49..8610.49 rows=84349 width=29) (actual time=76.249..76.249 rows=77162 loops=1138)
                                       ->  Seq Scan on rhnpackage p  (cost=0.00..8610.49 rows=84349 width=29) (actual time=0.005..40.578 rows=77162 loops=1138)
                     ->  Index Scan using rhn_pe_id_pk on rhnpackageevr pe  (cost=0.00..8.27 rows=1 width=58) (actual time=0.018..0.018 rows=1 loops=1138)
                           Index Cond: (pe.id = sp.evr_id)
               ->  Seq Scan on rhnpackagearch pa  (cost=0.00..1.45 rows=45 width=17) (actual time=0.006..0.012 rows=46 loops=1138)
         ->  Index Scan using rhn_pn_id_pk on rhnpackagename pn  (cost=0.00..8.27 rows=1 width=22) (actual time=0.011..0.012 rows=1 loops=1138)
               Index Cond: (pn.id = sp.name_id)
 Total runtime: 130823.753 ms
(28 rows)

It's also not clear to me why rhnPackageName, rhnPackageEvr, and rhnPackageArch are joined to rhnServerPackage with a left join instead of an inner join, since the columns in rhnServerPackage are all required to be non-null and have foreign key constraints on the corresponding tables:

rhnschema=# \d rhnServerPackage
                   Table "public.rhnserverpackage"
     Column      |           Type           |       Modifiers        
-----------------+--------------------------+------------------------
 server_id       | numeric                  | not null
 name_id         | numeric                  | not null
 evr_id          | numeric                  | not null
 package_arch_id | numeric                  | 
 created         | timestamp with time zone | not null default now()
 installtime     | timestamp with time zone | 
Indexes:
    "rhn_sp_snep_uq" UNIQUE, btree (server_id, name_id, evr_id, package_arch_id)
Foreign-key constraints:
    "rhnserverpackage_evr_id_fkey" FOREIGN KEY (evr_id) REFERENCES rhnpackageevr(id)
    "rhnserverpackage_name_id_fkey" FOREIGN KEY (name_id) REFERENCES rhnpackagename(id)
    "rhnserverpackage_package_arch_id_fkey" FOREIGN KEY (package_arch_id) REFERENCES rhnpackagearch(id)
    "rhnserverpackage_server_id_fkey" FOREIGN KEY (server_id) REFERENCES rhnserver(id) ON DELETE CASCADE


I think these left joins should be replaced with inner joins, and the first left join should be improved so that it (1) returns a matching package id if available, regardless of whether the system is subscribed to the channel that provides it, and (2) doesn't take a couple minutes to complete.