Plugins » Katello

Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1413218

Description of problem:

With candlepin 0.9.54, the following query pops up as taking over 1000msec:

select count(this_.id) as y0_ from cp_consumer this_ inner join cp_consumer_facts f1_ on this_.id=f1_.cp_consumer_id where this_.owner_id=$1 and f1_.mapkey ilike $2 and f1_.element ilike $3

parameters: $1 = '8a98d3fd568e316701568e31cd1d0001', $2 = 'virt.is_guest', $3 = 'true'

This was on a DB with 20K systems in a single org.

It looks like this is happening around https://github.com/candlepin/candlepin/blob/master/server/src/main/java/org/candlepin/model/OwnerInfoCurator.java#L114-L119.

this is called by the katello dashboard which is updated every 5 seconds, so if the query can be improved, it would reduce postgres load and make the dashboard faster.

I don't have a specific hard target for this, but 100msec or less would be great.

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

I added the following index which reduced the time to 33% of the previous time, but it's still > 500 msec:

create index fact_idx_1 on cp_consumer_facts(mapkey) where mapkey ilike 'virt.is_guest';