Description of problem: When I register RHEL5-Server-GOLD system into PostgreSQL satellite and Oracle one, displaying page Systems -> <my_system> -> Software -> Packages -> Extra Packages takes ~33 seconds on PostgreSQL and ~1 second on Oracle. Version-Release number of selected component (if applicable): spacewalk-schema-2.0.2-11.el6sat.noarch satellite-schema-5.6.0.10-1.el6sat.noarch spacewalk-postgresql-2.0.1-3.el6sat.noarch postgresql-server-8.4.13-1.el6_3.x86_64 How reproducible: always on mine setup Steps to Reproduce: 1. Have Satellite 5.6.0 with RHEL5 and RHEL6 channels synced 2. Register RHEL5-Server-GOLD 3. Go to Systems -> <my_system> -> Software -> Packages -> Extra Packages Actual results: Takes ~33 seconds on PostgreSQL and ~1 second on Oracle variants. Expected results: Times should be +- equal as HW for both variants is equal. Additional info: With DB set to log queries longer than 1000ms it logs this: 2013-09-06 03:01:42.174 EDT LOG: duration: 33464.016 ms execute <unnamed>: 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 = $1 and (scp.package_id is null) order by nvre 2013-09-06 03:01:42.174 EDT DETAIL: parameters: $1 = '1000010052'
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