Description of problem: We are using the postgresql backend. If you try to delete more than 2 errata at once the postgresql server goes full load on one core and the webui becomes unresponsive. Steps to Reproduce: 1. use postgresql backend 2. delete more than 3 errata at once in the webui (https://fqdn/rhn/channels/manage/errata/ConfirmRemove.do?cid=...) Actual results: The postgresql backend goes full load while running this query: select package1_.id as col_0_0_ from rhnChannel channel0_, rhnPackage package1_, rhnErrata publishede2_ where (package1_.id in (select package4_.id from rhnChannelPackage packages3_, rhnPackage package4_ where channel0_.id=packages3_.channel_id and packages3_.package_id=package4_.id)) and (package1_.id in (select package6_.id from rhnErrataPackage packages5_, rhnPackage package6_ where publishede2_.id=packages5_.errata_id and pack ages5_.package_id=package6_.id)) and channel0_.id=$1 and (publishede2_.id in ($2 , $3 , $4)) Additional info: Vacuum, analyze and reindex have already been done. An explain yielded the following explaination: explain select package1_.id as col_0_0_ from rhnChannel channel0_, rhnPackage package1_, rhnErrata publishede2_ where (package1_.id in (select package4_.id from rhnChannelPackage packages3_, rhnPackage package4_ where channel0_.id=packages3_.channel_id and packages3_.package_id=package4_.id)) and (package1_.id in (select package6_.id from rhnErrataPackage packages5_, rhnPackage package6_ where publishede2_.id=packages5_.errata_id and packages5_.package_id=package6_.id)) and channel0_.id=291 and (publishede2_.id in (12826 , 12654 , 12912)) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=12.78..233562123.67 rows=44121 width=8) Join Filter: (SubPlan 2) -> Nested Loop (cost=0.00..231202076.15 rows=29414 width=8) Join Filter: (SubPlan 1) -> Index Scan using rhn_channel_id_pk on rhnchannel channel0_ (cost=0.00..8.27 rows=1 width=7) Index Cond: (id = 291::numeric) -> Seq Scan on rhnpackage package1_ (cost=0.00..5574.27 rows=58827 width=8) SubPlan 1 -> Hash Join (cost=652.05..7202.96 rows=2070 width=8) Hash Cond: (package4_.id = packages3_.package_id) -> Seq Scan on rhnpackage package4_ (cost=0.00..5574.27 rows=58827 width=8) -> Hash (cost=626.18..626.18 rows=2070 width=8) -> Bitmap Heap Scan on rhnchannelpackage packages3_ (cost=52.30..626.18 rows=2070 width=8) Recheck Cond: ($0 = channel_id) -> Bitmap Index Scan on rhn_cp_cp_uq (cost=0.00..51.79 rows=2070 width=0) Index Cond: ($0 = channel_id) -> Bitmap Heap Scan on rhnerrata publishede2_ (cost=12.78..23.99 rows=3 width=7) Recheck Cond: (publishede2_.id = ANY ('{12826,12654,12912}'::numeric[])) -> Bitmap Index Scan on rhn_errata_id_pk (cost=0.00..12.77 rows=3 width=0) Index Cond: (publishede2_.id = ANY ('{12826,12654,12912}'::numeric[])) SubPlan 2 -> Nested Loop (cost=0.00..37.47 rows=3 width=8) -> Index Scan using rhn_err_pkg_eid_pid_uq on rhnerratapackage packages5_ (cost=0.00..12.60 rows=3 width=8) Index Cond: ($2 = errata_id) -> Index Scan using rhn_package_id_pk on rhnpackage package6_ (cost=0.00..8.28 rows=1 width=8) Index Cond: (package6_.id = packages5_.package_id) (26 rows)
I can't reproduce the issue. Anyway the query created by hibernate is awful and has huge cost. So I optimized query manually. commit d194cc14de0a874cc2ea2f19cb1b2df1e1681772 863123 - improved query
Fixing the fixed query ... :-) spacewalk.git: 460b428823a705196396681c3b9358fad467ab32
Marking bug as ON_QA since tonight's build of Spacewalk nightly is a release candidate for Spacewalk 1.9.
Spacewalk 1.9 has been released. https://fedorahosted.org/spacewalk/wiki/ReleaseNotes19