Bug 1630531

Created attachment 1484500 [details]
explain plan

Description of problem:
There is a slow query appearing from time to time with 7500 registered hosts.


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


How reproducible:


Steps to Reproduce:
1. Have 7500 hosts registered and continue to register in waves of 5
2. I have used containers running something like:
usr/lib/systemd/systemd --system --unit=basic.target &
sleep 1

yum -y install 'https://inecas.fedorapeople.org/fakerepos/zoo3/walrus-0.71-1.noarch.rpm' 'https://inecas.fedorapeople.org/fakerepos/zoo3/shark-0.1-1.noarch.rpm' 'https://inecas.fedorapeople.org/fakerepos/zoo3/stork-0.12-2.noarch.rpm' 'https://inecas.fedorapeople.org/fakerepos/zoo3/whale-0.2-1.noarch.rpm'
rm -f /etc/pki/product/* /etc/pki/product-default/*

rpm -ivh http://$SATSERVER/pub/katello-ca-consumer-latest.noarch.rpm
while true; do
    subscription-manager clean
    rm -f /etc/rhsm/facts/katello.facts
    hostnamectl set-hostname something-$RANDOM-$RANDOM.example.com
    subscription-manager register --org Default_Organization --activationkey BZ1515093
done
3. Enable logging of slow queries (needs DB restart or reload):
    # grep log_min_duration_statement /var/lib/pgsql/data/postgresql.conf
    log_min_duration_statement = 1000


Actual results:
In about 5 hours (between 2500 hosts registered mark and 7500 mark), I got this reported 8 times:

LOG:  duration: 1064.583 ms  execute <unnamed>: select this_.id as id1_36_19_, this_.created as created2_36_19_, this_.updated as updated3_36_19_, this_.consumer_id as consumer8_36_19_, this_.dirty as dirty4_36_19_, this_.endDateOverride as endDateO5_36_19_, this_.owner_id as owner_id9_36_19_, this_.pool_id as pool_id10_36_19_, this_.quantity as quantity6_36_19_, this_.updatedOnStart as updatedO7_36_19_, consumer1_.id as id1_22_0_, consumer1_.created as created2_22_0_, consumer1_.updated as updated3_22_0_, consumer1_.annotations as annotati4_22_0_, consumer1_.autoheal as autoheal5_22_0_, consumer1_.complianceStatusHash as complian6_22_0_, consumer1_.cont_acc_cert_id as cont_ac20_22_0_, consumer1_.content_access_mode as content_7_22_0_, consumer1_.entitlement_count as entitlem8_22_0_, consumer1_.entitlementStatus as entitlem9_22_0_, consumer1_.environment_id as environ10_22_0_, consumer1_.consumer_idcert_id as consume21_22_0_, consumer1_.keyPair_id as keyPair22_22_0_, consumer1_.lastCheckin as lastChe11_22_0_, consumer1_.name as name12_22_0_, consumer1_.owner_id as owner_i13_22_0_, consumer1_.recipient_owner_key as recipie14_22_0_, consumer1_.releaseVer as release15_22_0_, consumer1_.serviceLevel as service16_22_0_, consumer1_.type_id as type_id17_22_0_, consumer1_.username as usernam18_22_0_, consumer1_.uuid as uuid19_22_0_, identityce4_.id as id1_40_1_, identityce4_.created as created2_40_1_, identityce4_.updated as updated3_40_1_, identityce4_.cert as cert4_40_1_, identityce4_.privatekey as privatek5_40_1_, identityce4_.serial_id as serial_i6_40_1_, certificat5_.id as id1_20_2_, certificat5_.created as created2_20_2_, certificat5_.updated as updated3_20_2_, certificat5_.collected as collecte4_20_2_, certificat5_.expiration as expirati5_20_2_, certificat5_.revoked as revoked6_20_2_, hypervisor6_.id as id1_28_3_, hypervisor6_.created as created2_28_3_, hypervisor6_.updated as updated3_28_3_, hypervisor6_.consumer_id as consumer6_28_3_, hypervisor6_.hypervisor_id as hypervis4_28_3_, hypervisor6_.owner_id as owner_id7_28_3_, hypervisor6_.reporter_id as reporter5_28_3_, owner7_.id as id1_47_4_, owner7_.created as created2_47_4_, owner7_.updated as updated3_47_4_, owner7_.autobind_disabled as autobind4_47_4_, owner7_.content_access_mode as content_5_47_4_, owner7_.content_access_mode_list as content_6_47_4_, owner7_.contentPrefix as contentP7_47_4_, owner7_.defaultServiceLevel as defaultS8_47_4_, owner7_.displayName as displayN9_47_4_, owner7_.account as account10_47_4_, owner7_.last_refreshed as last_re11_47_4_, owner7_.logLevel as logLeve12_47_4_, owner7_.parent_owner as parent_13_47_4_, owner7_.upstream_id as upstrea14_47_4_, owner8_.id as id1_47_5_, owner8_.created as created2_47_5_, owner8_.updated as updated3_47_5_, owner8_.autobind_disabled as autobind4_47_5_, owner8_.content_access_mode as content_5_47_5_, owner8_.content_access_mode_list as content_6_47_5_, owner8_.contentPrefix as contentP7_47_5_, owner8_.defaultServiceLevel as defaultS8_47_5_, owner8_.displayName as displayN9_47_5_, owner8_.account as account10_47_5_, owner8_.last_refreshed as last_re11_47_5_, owner8_.logLevel as logLeve12_47_5_, owner8_.parent_owner as parent_13_47_5_, owner8_.upstream_id as upstrea14_47_5_, pool9_.id as id1_50_6_, pool9_.created as created2_50_6_, pool9_.updated as updated3_50_6_, pool9_.accountNumber as accountN4_50_6_, pool9_.activeSubscription as activeSu5_50_6_, pool9_.cdn_id as cdn_id21_50_6_, pool9_.certificate_id as certifi22_50_6_, pool9_.quantity_consumed as quantity6_50_6_, pool9_.contractNumber as contract7_50_6_, pool9_.created_by_share as created_8_50_6_, pool9_.derived_product_uuid as derived23_50_6_, pool9_.endDate as endDate9_50_6_, pool9_.quantity_exported as quantit10_50_6_, pool9_.has_shared_ancestor as has_sha11_50_6_, pool9_.orderNumber as orderNu12_50_6_, pool9_.owner_id as owner_i24_50_6_, pool9_.product_uuid as product25_50_6_, pool9_.quantity as quantit13_50_6_, pool9_.restrictedToUsername as restric14_50_6_, pool9_.quantity_shared as quantit15_50_6_, pool9_.sourceEntitlement_id as sourceE26_50_6_, pool9_.startDate as startDa16_50_6_, pool9_.type as type17_50_6_, pool9_.upstream_consumer_id as upstrea18_50_6_, pool9_.upstream_entitlement_id as upstrea19_50_6_, pool9_.upstream_pool_id as upstrea20_50_6_, cdn10_.id as id1_18_7_, cdn10_.created as created2_18_7_, cdn10_.updated as updated3_18_7_, cdn10_.certificate_id as certific7_18_7_, cdn10_.label as label4_18_7_, cdn10_.name as name5_18_7_, cdn10_.url as url6_18_7_, cdncertifi11_.id as id1_19_8_, cdncertifi11_.created as created2_19_8_, cdncertifi11_.updated as updated3_19_8_, cdncertifi11_.cert as cert4_19_8_, cdncertifi11_.privatekey as privatek5_19_8_, cdncertifi11_.serial_id as serial_i6_19_8_, certificat12_.id as id1_20_9_, certificat12_.created as created2_20_9_, certificat12_.updated as updated3_20_9_, certificat12_.collected as collecte4_20_9_, certificat12_.expiration as expirati5_20_9_, certificat12_.revoked as revoked6_20_9_, subscripti13_.id as id1_21_10_, subscripti13_.created as created2_21_10_, subscripti13_.updated as updated3_21_10_, subscripti13_.cert as cert4_21_10_, subscripti13_.privatekey as privatek5_21_10_, subscripti13_.serial_id as serial_i6_21_10_, certificat14_.id as id1_20_11_, certificat14_.created as created2_20_11_, certificat14_.updated as updated3_20_11_, certificat14_.collected as collecte4_20_11_, certificat14_.expiration as expirati5_20_11_, certificat14_.revoked as revoked6_20_11_, product15_.uuid as uuid1_14_12_, product15_.created as created2_14_12_, product15_.updated as updated3_14_12_, product15_.entity_version as entity_v4_14_12_, product15_.product_id as product_5_14_12_, product15_.locked as locked6_14_12_, product15_.multiplier as multipli7_14_12_, product15_.name as name8_14_12_, owner16_.id as id1_47_13_, owner16_.created as created2_47_13_, owner16_.updated as updated3_47_13_, owner16_.autobind_disabled as autobind4_47_13_, owner16_.content_access_mode as content_5_47_13_, owner16_.content_access_mode_list as content_6_47_13_, owner16_.contentPrefix as contentP7_47_13_, owner16_.defaultServiceLevel as defaultS8_47_13_, owner16_.displayName as displayN9_47_13_, owner16_.account as account10_47_13_, owner16_.last_refreshed as last_re11_47_13_, owner16_.logLevel as logLeve12_47_13_, owner16_.parent_owner as parent_13_47_13_, owner16_.upstream_id as upstrea14_47_13_, product17_.uuid as uuid1_14_14_, product17_.created as created2_14_14_, product17_.updated as updated3_14_14_, product17_.entity_version as entity_v4_14_14_, product17_.product_id as product_5_14_14_, product17_.locked as locked6_14_14_, product17_.multiplier as multipli7_14_14_, product17_.name as name8_14_14_, entitlemen18_.id as id1_36_15_, entitlemen18_.created as created2_36_15_, entitlemen18_.updated as updated3_36_15_, entitlemen18_.consumer_id as consumer8_36_15_, entitlemen18_.dirty as dirty4_36_15_, entitlemen18_.endDateOverride as endDateO5_36_15_, entitlemen18_.owner_id as owner_id9_36_15_, entitlemen18_.pool_id as pool_id10_36_15_, entitlemen18_.quantity as quantity6_36_15_, entitlemen18_.updatedOnStart as updatedO7_36_15_, consumer19_.id as id1_22_16_, consumer19_.created as created2_22_16_, consumer19_.updated as updated3_22_16_, consumer19_.annotations as annotati4_22_16_, consumer19_.autoheal as autoheal5_22_16_, consumer19_.complianceStatusHash as complian6_22_16_, consumer19_.cont_acc_cert_id as cont_ac20_22_16_, consumer19_.content_access_mode as content_7_22_16_, consumer19_.entitlement_count as entitlem8_22_16_, consumer19_.entitlementStatus as entitlem9_22_16_, consumer19_.environment_id as environ10_22_16_, consumer19_.consumer_idcert_id as consume21_22_16_, consumer19_.keyPair_id as keyPair22_22_16_, consumer19_.lastCheckin as lastChe11_22_16_, consumer19_.name as name12_22_16_, consumer19_.owner_id as owner_i13_22_16_, consumer19_.recipient_owner_key as recipie14_22_16_, consumer19_.releaseVer as release15_22_16_, consumer19_.serviceLevel as service16_22_16_, consumer19_.type_id as type_id17_22_16_, consumer19_.username as usernam18_22_16_, consumer19_.uuid as uuid19_22_16_, sourcestac20_.id as id1_53_17_, sourcestac20_.created as created2_53_17_, sourcestac20_.updated as updated3_53_17_, sourcestac20_.derivedPool_id as derivedP5_53_17_, sourcestac20_.sourceConsumer_id as sourceCo6_53_17_, sourcestac20_.sourceStackId as sourceSt4_53_17_, sourcesubs21_.id as id1_8_18_, sourcesubs21_.created as created2_8_18_, sourcesubs21_.updated as updated3_8_18_, sourcesubs21_.pool_id as pool_id6_8_18_, sourcesubs21_.subscription_id as subscrip4_8_18_, sourcesubs21_.subscription_sub_key as subscrip5_8_18_ from cp_entitlement this_ inner join cp_consumer consumer1_ on this_.consumer_id=consumer1_.id left outer join cp_id_cert identityce4_ on consumer1_.consumer_idcert_id=identityce4_.id left outer join cp_cert_serial certificat5_ on identityce4_.serial_id=certificat5_.id left outer join cp_consumer_hypervisor hypervisor6_ on consumer1_.id=hypervisor6_.consumer_id inner join cp_owner owner7_ on this_.owner_id=owner7_.id left outer join cp_owner owner8_ on owner7_.parent_owner=owner8_.id left outer join cp_pool pool9_ on this_.pool_id=pool9_.id left outer join cp_cdn cdn10_ on pool9_.cdn_id=cdn10_.id left outer join cp_cdn_certificate cdncertifi11_ on cdn10_.certificate_id=cdncertifi11_.id left outer join cp_cert_serial certificat12_ on cdncertifi11_.serial_id=certificat12_.id left outer join cp_certificate subscripti13_ on pool9_.certificate_id=subscripti13_.id left outer join cp_cert_serial certificat14_ on subscripti13_.serial_id=certificat14_.id left outer join cp2_products product15_ on pool9_.derived_product_uuid=product15_.uuid left outer join cp_owner owner16_ on pool9_.owner_id=owner16_.id left outer join cp2_products product17_ on pool9_.product_uuid=product17_.uuid left outer join cp_entitlement entitlemen18_ on pool9_.sourceEntitlement_id=entitlemen18_.id left outer join cp_consumer consumer19_ on entitlemen18_.consumer_id=consumer19_.id left outer join cp_pool_source_stack sourcestac20_ on pool9_.id=sourcestac20_.derivedPool_id left outer join cp2_pool_source_sub sourcesubs21_ on pool9_.id=sourcesubs21_.pool_id where consumer1_.environment_id=$1


Expected results:
No queries above a second.


Additional info:
Explain plan attached