Attachment 1484500 Details for Bug 1630531 – explain plan

explain plan

file_1630531.txt (text/plain), 19.46 KB, created by Jan Hutař on 2018-09-18 21:00:03 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jan Hutař

Created: 2018-09-18 21:00:03 UTC

Size: 19.46 KB

patch

obsolete

>candlepin=# EXPLAIN ANALYZE 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 = '151';
>                                                                                                                                 QUERY PLAN                                                                                                   
>                              
>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>------------------------------
> Nested Loop Left Join  (cost=0.00..130.79 rows=2 width=22809) (actual time=0.040..0.040 rows=0 loops=1)
>   ->  Nested Loop Left Join  (cost=0.00..123.04 rows=2 width=22031) (actual time=0.040..0.040 rows=0 loops=1)
>         ->  Nested Loop Left Join  (cost=0.00..116.08 rows=2 width=21253) (actual time=0.040..0.040 rows=0 loops=1)
>               ->  Nested Loop Left Join  (cost=0.00..115.23 rows=2 width=19234) (actual time=0.040..0.040 rows=0 loops=1)
>                     ->  Nested Loop Left Join  (cost=0.00..98.66 rows=2 width=19072) (actual time=0.040..0.040 rows=0 loops=1)
>                           ->  Nested Loop Left Join  (cost=0.00..88.51 rows=2 width=18958) (actual time=0.040..0.040 rows=0 loops=1)
>                                 ->  Nested Loop Left Join  (cost=0.00..80.75 rows=2 width=15509) (actual time=0.040..0.040 rows=0 loops=1)
>                                       ->  Nested Loop Left Join  (cost=0.00..70.60 rows=2 width=15395) (actual time=0.038..0.038 rows=0 loops=1)
>                                             ->  Nested Loop Left Join  (cost=0.00..62.88 rows=2 width=15361) (actual time=0.038..0.038 rows=0 loops=1)
>                                                   ->  Nested Loop Left Join  (cost=0.00..55.12 rows=2 width=15191) (actual time=0.038..0.038 rows=0 loops=1)
>                                                         ->  Nested Loop Left Join  (cost=0.00..47.40 rows=2 width=15157) (actual time=0.038..0.038 rows=0 loops=1)
>                                                               ->  Nested Loop Left Join  (cost=0.00..44.65 rows=2 width=14987) (actual time=0.038..0.038 rows=0 loops=1)
>                                                                     ->  Nested Loop Left Join  (cost=0.00..37.69 rows=2 width=13259) (actual time=0.038..0.038 rows=0 loops=1)
>                                                                           ->  Nested Loop Left Join  (cost=0.00..37.13 rows=2 width=12162) (actual time=0.038..0.038 rows=0 loops=1)
>                                                                                 ->  Nested Loop  (cost=0.00..31.78 rows=2 width=8713) (actual time=0.038..0.038 rows=0 loops=1)
>                                                                                       ->  Nested Loop  (cost=0.00..31.21 rows=2 width=5264) (actual time=0.038..0.038 rows=0 loops=1)
>                                                                                             ->  Nested Loop Left Join  (cost=0.00..21.40 rows=1 width=5102) (actual time=0.037..0.037 rows=0 loops=1)
>                                                                                                   ->  Nested Loop Left Join  (cost=0.00..13.12 rows=1 width=3808) (actual time=0.037..0.037 rows=0 loops=1)
>                                                                                                         ->  Nested Loop Left Join  (cost=0.00..12.55 rows=1 width=3774) (actual time=0.037..0.037 rows=0 loops=1)
>                                                                                                               ->  Index Scan using cp_consumer_environment_id_idx on cp_consumer consumer1_  (cost=0.00..4.27 rows=1 width=2019) (actual time
>=0.037..0.037 rows=0 loops=1)
>                                                                                                                     Index Cond: ((environment_id)::text = '151'::text)
>                                                                                                               ->  Index Scan using cp_id_cert_pkey on cp_id_cert identityce4_  (cost=0.00..8.27 rows=1 width=1755) (never executed)
>                                                                                                                     Index Cond: ((consumer1_.consumer_idcert_id)::text = (id)::text)
>                                                                                                         ->  Index Scan using cp_cert_serial_pkey on cp_cert_serial certificat5_  (cost=0.00..0.56 rows=1 width=34) (never executed)
>                                                                                                               Index Cond: (identityce4_.serial_id = id)
>                                                                                                   ->  Index Scan using cp_consmr_hyprvsr_consmr_ukey on cp_consumer_hypervisor hypervisor6_  (cost=0.00..8.27 rows=1 width=1294) (never execu
>ted)
>                                                                                                         Index Cond: ((consumer1_.id)::text = (consumer_id)::text)
>                                                                                             ->  Index Scan using cp_entitlement_consumer_id_idx on cp_entitlement this_  (cost=0.00..9.79 rows=2 width=162) (never executed)
>                                                                                                   Index Cond: ((consumer_id)::text = (consumer1_.id)::text)
>                                                                                       ->  Index Scan using cp_owner_pkey on cp_owner owner7_  (cost=0.00..0.27 rows=1 width=3449) (never executed)
>                                                                                             Index Cond: ((id)::text = (this_.owner_id)::text)
>                                                                                 ->  Index Scan using cp_owner_pkey on cp_owner owner8_  (cost=0.00..2.67 rows=1 width=3449) (never executed)
>                                                                                       Index Cond: ((owner7_.parent_owner)::text = (id)::text)
>                                                                           ->  Index Scan using cp_pool_subid_subsubkey_key on cp_pool pool9_  (cost=0.00..0.27 rows=1 width=1097) (never executed)
>                                                                                 Index Cond: ((this_.pool_id)::text = (id)::text)
>                                                                     ->  Index Scan using cp_cdn_pkey on cp_cdn cdn10_  (cost=0.00..3.47 rows=1 width=1728) (never executed)
>                                                                           Index Cond: ((pool9_.cdn_id)::text = (id)::text)
>                                                               ->  Index Scan using cp_cdn_certificate_pkey on cp_cdn_certificate cdncertifi11_  (cost=0.00..1.37 rows=1 width=170) (never executed)
>                                                                     Index Cond: ((cdn10_.certificate_id)::text = (id)::text)
>                                                         ->  Index Scan using cp_cert_serial_pkey on cp_cert_serial certificat12_  (cost=0.00..3.85 rows=1 width=34) (never executed)
>                                                               Index Cond: (cdncertifi11_.serial_id = id)
>                                                   ->  Index Scan using cp_certificate_pkey on cp_certificate subscripti13_  (cost=0.00..3.87 rows=1 width=170) (never executed)
>                                                         Index Cond: ((pool9_.certificate_id)::text = (id)::text)
>                                             ->  Index Scan using cp_cert_serial_pkey on cp_cert_serial certificat14_  (cost=0.00..3.85 rows=1 width=34) (never executed)
>                                                   Index Cond: (subscripti13_.serial_id = id)
>                                       ->  Index Scan using cp2_products_pk on cp2_products product15_  (cost=0.00..5.07 rows=1 width=114) (never executed)
>                                             Index Cond: ((pool9_.derived_product_uuid)::text = (uuid)::text)
>                                 ->  Index Scan using cp_owner_pkey on cp_owner owner16_  (cost=0.00..3.87 rows=1 width=3449) (never executed)
>                                       Index Cond: ((pool9_.owner_id)::text = (id)::text)
>                           ->  Index Scan using cp2_products_pk on cp2_products product17_  (cost=0.00..5.07 rows=1 width=114) (never executed)
>                                 Index Cond: ((pool9_.product_uuid)::text = (uuid)::text)
>                     ->  Index Scan using cp_entitlement_pkey on cp_entitlement entitlemen18_  (cost=0.00..8.27 rows=1 width=162) (never executed)
>                           Index Cond: ((pool9_.sourceentitlement_id)::text = (id)::text)
>               ->  Index Scan using cp_consumer_pkey on cp_consumer consumer19_  (cost=0.00..0.41 rows=1 width=2019) (never executed)
>                     Index Cond: ((entitlemen18_.consumer_id)::text = (id)::text)
>         ->  Index Scan using idx_sourcestack_pool_fk on cp_pool_source_stack sourcestac20_  (cost=0.00..3.47 rows=1 width=778) (never executed)
>               Index Cond: ((pool9_.id)::text = (derivedpool_id)::text)
>   ->  Index Scan using cp2_pool_source_sub_unq1 on cp2_pool_source_sub sourcesubs21_  (cost=0.00..3.87 rows=1 width=778) (never executed)
>         Index Cond: ((pool9_.id)::text = (pool_id)::text)
> Total runtime: 0.993 ms
>(60 rows)

Actions: View

Attachments on bug 1630531: 1484500