explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1VmU

Settings
# exclusive inclusive rows x rows loops node
1. 1.397 87.942 ↓ 1.2 605 1

Unique (cost=73,519.11..73,554.18 rows=501 width=375) (actual time=86.427..87.942 rows=605 loops=1)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, mc.email, mc.status, m.description, m.licensetype, slp.startdate, lp.license_id, lp.partner_id, lp.enddate, lp.created, pfu.productfeature_id, pfu.unitcount, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pf.featurekey, pfu.starter, ((pricingplan.monthsvalid = 12)), (CASE WHEN (lp.partner_id = 1,652,472) THEN true ELSE false END)
2. 9.913 86.545 ↓ 4.7 2,330 1

Sort (cost=73,519.11..73,520.37 rows=501 width=375) (actual time=86.426..86.545 rows=2,330 loops=1)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, mc.email, mc.status, m.description, m.licensetype, slp.startdate, lp.license_id, lp.partner_id, lp.enddate, lp.created, pfu.productfeature_id, pfu.unitcount, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pf.featurekey, pfu.starter, ((pricingplan.monthsvalid = 12)), (CASE WHEN (lp.partner_id = 1,652,472) THEN true ELSE false END)
  • Sort Key: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, mc.email, mc.status, m.description, m.licensetype, slp.startdate, lp.license_id, lp.partner_id, lp.enddate, lp.created, pfu.productfeature_id, pfu.unitcount, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pf.featurekey, pfu.starter, ((pricingplan.monthsvalid = 12)), (CASE WHEN (lp.partner_id = 1,652,472) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 1,109kB
3. 3.515 76.632 ↓ 4.7 2,330 1

Gather (cost=3,744.67..73,496.65 rows=501 width=375) (actual time=25.975..76.632 rows=2,330 loops=1)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, mc.email, mc.status, m.description, m.licensetype, slp.startdate, lp.license_id, lp.partner_id, lp.enddate, lp.created, pfu.productfeature_id, pfu.unitcount, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pf.featurekey, pfu.starter, ((pricingplan.monthsvalid = 12)), (CASE WHEN (lp.partner_id = 1,652,472) THEN true ELSE false END)
  • Workers Planned: 1
  • Workers Launched: 1
4. 0.376 73.117 ↓ 3.9 1,165 2 / 2

Nested Loop Left Join (cost=2,744.67..72,445.52 rows=295 width=375) (actual time=24.164..73.117 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, mc.email, mc.status, m.description, m.licensetype, slp.startdate, lp.license_id, lp.partner_id, lp.enddate, lp.created, pfu.productfeature_id, pfu.unitcount, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pf.featurekey, pfu.starter, (pricingplan.monthsvalid = 12), CASE WHEN (lp.partner_id = 1,652,472) THEN true ELSE false END
  • Inner Unique: true
  • Worker 0: actual time=23.512..72.345 rows=1,147 loops=1
5. 0.434 70.411 ↓ 3.9 1,165 2 / 2

Hash Left Join (cost=2,744.24..70,846.87 rows=295 width=354) (actual time=24.150..70.411 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, slp.startdate, m.description, m.licensetype, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pricingplan.monthsvalid, pfu.productfeature_id, pfu.unitcount, pfu.starter, pf.featurekey, mcr.contact_id
  • Inner Unique: true
  • Hash Cond: (pfu.productfeature_id = pf.id)
  • Worker 0: actual time=23.498..69.697 rows=1,147 loops=1
6. 1.053 68.781 ↓ 3.9 1,165 2 / 2

Nested Loop Left Join (cost=2,580.98..70,682.83 rows=295 width=344) (actual time=22.908..68.781 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, slp.startdate, m.description, m.licensetype, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pricingplan.monthsvalid, pfu.productfeature_id, pfu.unitcount, pfu.starter, mcr.contact_id
  • Inner Unique: true
  • Worker 0: actual time=22.270..68.092 rows=1,147 loops=1
7. 0.932 65.398 ↓ 3.9 1,165 2 / 2

Nested Loop Left Join (cost=2,580.41..69,009.75 rows=295 width=339) (actual time=22.896..65.398 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, lp.productfeatureusage_id, slp.startdate, m.description, m.licensetype, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, pricingplan.monthsvalid, mcr.contact_id
  • Inner Unique: true
  • Worker 0: actual time=22.259..64.775 rows=1,147 loops=1
8. 0.253 52.816 ↓ 3.9 1,165 2 / 2

Nested Loop (cost=2,580.12..67,464.32 rows=295 width=335) (actual time=22.699..52.816 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, lp.productfeatureusage_id, slp.startdate, m.description, m.licensetype, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description, mcr.contact_id
  • Inner Unique: true
  • Worker 0: actual time=22.245..51.537 rows=1,147 loops=1
9. 0.316 47.903 ↓ 2.4 1,165 2 / 2

Hash Left Join (cost=2,579.55..65,677.98 rows=495 width=343) (actual time=22.683..47.903 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, p.account_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, lp.productfeatureusage_id, slp.startdate, slp.account_id, m.description, m.licensetype, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpackparent.key, prodpackparent.description
  • Inner Unique: true
  • Hash Cond: (prodpack.parent_id = prodpackparent.id)
  • Worker 0: actual time=22.229..46.625 rows=1,147 loops=1
10. 0.476 44.978 ↓ 2.4 1,165 2 / 2

Hash Left Join (cost=2,120.99..65,218.12 rows=495 width=280) (actual time=20.036..44.978 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, p.account_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, lp.productfeatureusage_id, slp.startdate, slp.account_id, m.description, m.licensetype, prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpack.parent_id
  • Inner Unique: true
  • Hash Cond: (m.productpackage_id = prodpack.id)
  • Worker 0: actual time=19.681..43.791 rows=1,147 loops=1
11. 0.691 40.840 ↓ 2.4 1,165 2 / 2

Hash Join (cost=1,662.43..64,758.26 rows=495 width=190) (actual time=16.308..40.840 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, p.account_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, lp.productfeatureusage_id, slp.startdate, slp.account_id, m.description, m.licensetype, m.productpackage_id
  • Inner Unique: true
  • Hash Cond: (a.merchandise_id = m.id)
  • Worker 0: actual time=15.840..39.540 rows=1,147 loops=1
12. 0.981 24.646 ↓ 2.4 1,165 2 / 2

Nested Loop (cost=68.57..63,163.10 rows=495 width=105) (actual time=0.471..24.646 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, p.account_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, lp.productfeatureusage_id, slp.startdate, slp.account_id
  • Join Filter: (p.account_id = slp.account_id)
  • Worker 0: actual time=0.059..23.408 rows=1,147 loops=1
13. 0.953 10.850 ↓ 2.4 1,165 2 / 2

Nested Loop (cost=68.00..24,014.46 rows=495 width=89) (actual time=0.460..10.850 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, p.account_id, lp.license_id, lp.partner_id, lp.enddate, lp.created, lp.productfeatureusage_id
  • Inner Unique: true
  • Worker 0: actual time=0.047..10.188 rows=1,147 loops=1
14. 1.436 7.567 ↓ 2.4 1,165 2 / 2

Nested Loop (cost=67.43..21,097.69 rows=495 width=58) (actual time=0.451..7.567 rows=1,165 loops=2)

  • Output: a.id, a.pricingplan_id, a.name, a.priceoverridetype, a.merchandise_id, a.latestperiod_id, p.account_id
  • Inner Unique: true
  • Worker 0: actual time=0.036..6.967 rows=1,147 loops=1
15. 1.336 1.644 ↓ 1.6 2,244 2 / 2

Parallel Bitmap Heap Scan on public.period p (cost=66.99..9,375.26 rows=1,398 width=16) (actual time=0.439..1.644 rows=2,244 loops=2)

  • Output: p.id, p.created, p.startdate, p.enddate, p.months, p.productid, p.current, p.referencenumber, p.invoice_id, p.account_id, p.license_id, p.orderable_id, p.previousperiod_id, p.autoquoted, p.partner_id, p.renewreminded, p.pricingplan_id, p.productfeatureusage_id, p.dbmodified, p.renewalpricingplan_id, p.anchor, p.migrationevaluation_id
  • Recheck Cond: (p.partner_id = 1,652,472)
  • Heap Blocks: exact=1,041
  • Worker 0: actual time=0.023..1.167 rows=2,157 loops=1
16. 0.308 0.308 ↓ 2.0 4,647 1 / 2

Bitmap Index Scan on period_partner_id (cost=0.00..66.40 rows=2,377 width=0) (actual time=0.616..0.616 rows=4,647 loops=1)

  • Index Cond: (p.partner_id = 1,652,472)
17. 4.487 4.487 ↑ 1.0 1 4,487 / 2

Index Scan using account_pkey on public.account a (cost=0.44..8.39 rows=1 width=50) (actual time=0.002..0.002 rows=1 loops=4,487)

  • Output: a.id, a.name, a.autorenew, a.productid, a.domain, a.creditcarddata_id, a.merchandise_id, a.isjplicense, a.currentunits, a.latestperiod_id, a.status, a.unitusdoverridepriceextax, a.priceoverrideenddate, a.dbmodified, a.actualdomain, a.parent_id, a.renewalaction, a.externaluuid, a.pricingplan_id, a.createexternalaccount, a.warnbeforerenewal, a.priceoverridetype, a.archived, a.billing_group_id, a.transaction_account_billing_group_id, a.latestprovisioningoverrideperiod_id
  • Index Cond: (a.id = p.account_id)
  • Filter: ((a.parent_id IS NULL) AND ((a.status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 0
  • Worker 0: actual time=0.002..0.002 rows=1 loops=2,157
18. 2.330 2.330 ↑ 1.0 1 2,330 / 2

Index Scan using period_pkey on public.period lp (cost=0.57..5.89 rows=1 width=47) (actual time=0.002..0.002 rows=1 loops=2,330)

  • Output: lp.id, lp.created, lp.startdate, lp.enddate, lp.months, lp.productid, lp.current, lp.referencenumber, lp.invoice_id, lp.account_id, lp.license_id, lp.orderable_id, lp.previousperiod_id, lp.autoquoted, lp.partner_id, lp.renewreminded, lp.pricingplan_id, lp.productfeatureusage_id, lp.dbmodified, lp.renewalpricingplan_id, lp.anchor, lp.migrationevaluation_id
  • Index Cond: (lp.id = a.latestperiod_id)
  • Worker 0: actual time=0.002..0.002 rows=1 loops=1,147
19. 12.815 12.815 ↑ 5.0 1 2,330 / 2

Index Scan using period_account_id on public.period slp (cost=0.57..79.03 rows=5 width=16) (actual time=0.011..0.011 rows=1 loops=2,330)

  • Output: slp.id, slp.created, slp.startdate, slp.enddate, slp.months, slp.productid, slp.current, slp.referencenumber, slp.invoice_id, slp.account_id, slp.license_id, slp.orderable_id, slp.previousperiod_id, slp.autoquoted, slp.partner_id, slp.renewreminded, slp.pricingplan_id, slp.productfeatureusage_id, slp.dbmodified, slp.renewalpricingplan_id, slp.anchor, slp.migrationevaluation_id
  • Index Cond: (slp.account_id = a.id)
  • Filter: (slp.previousperiod_id IS NULL)
  • Rows Removed by Filter: 19
  • Worker 0: actual time=0.011..0.011 rows=1 loops=1,147
20. 9.173 15.503 ↑ 1.0 37,191 2 / 2

Hash (cost=1,128.94..1,128.94 rows=37,194 width=93) (actual time=15.503..15.503 rows=37,191 loops=2)

  • Output: m.description, m.licensetype, m.id, m.productpackage_id
  • Buckets: 65,536 Batches: 1 Memory Usage: 5,151kB
  • Worker 0: actual time=15.454..15.454 rows=37,191 loops=1
21. 6.330 6.330 ↑ 1.0 37,191 2 / 2

Seq Scan on public.merchandise m (cost=0.00..1,128.94 rows=37,194 width=93) (actual time=0.008..6.330 rows=37,191 loops=2)

  • Output: m.description, m.licensetype, m.id, m.productpackage_id
  • Worker 0: actual time=0.010..6.334 rows=37,191 loops=1
22. 1.682 3.662 ↓ 1.0 5,937 2 / 2

Hash (cost=384.36..384.36 rows=5,936 width=106) (actual time=3.662..3.662 rows=5,937 loops=2)

  • Output: prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpack.id, prodpack.parent_id
  • Buckets: 8,192 Batches: 1 Memory Usage: 894kB
  • Worker 0: actual time=3.778..3.778 rows=5,937 loops=1
23. 1.980 1.980 ↓ 1.0 5,937 2 / 2

Seq Scan on public.productpackage prodpack (cost=0.00..384.36 rows=5,936 width=106) (actual time=0.008..1.980 rows=5,937 loops=2)

  • Output: prodpack.pricingplanmodel, prodpack.key, prodpack.description, prodpack.producttype, prodpack.addon, prodpack.id, prodpack.parent_id
  • Worker 0: actual time=0.011..2.017 rows=5,937 loops=1
24. 1.423 2.609 ↓ 1.0 5,937 2 / 2

Hash (cost=384.36..384.36 rows=5,936 width=79) (actual time=2.609..2.609 rows=5,937 loops=2)

  • Output: prodpackparent.key, prodpackparent.description, prodpackparent.id
  • Buckets: 8,192 Batches: 1 Memory Usage: 734kB
  • Worker 0: actual time=2.509..2.509 rows=5,937 loops=1
25. 1.186 1.186 ↓ 1.0 5,937 2 / 2

Seq Scan on public.productpackage prodpackparent (cost=0.00..384.36 rows=5,936 width=79) (actual time=0.003..1.186 rows=5,937 loops=2)

  • Output: prodpackparent.key, prodpackparent.description, prodpackparent.id
  • Worker 0: actual time=0.003..1.103 rows=5,937 loops=1
26. 4.660 4.660 ↑ 4.0 1 2,330 / 2

Index Scan using contactrelationship_account on public.contactrelationship mcr (cost=0.56..12.75 rows=4 width=16) (actual time=0.004..0.004 rows=1 loops=2,330)

  • Output: mcr.id, mcr.main, mcr.relationshiptype, mcr.contact_id, mcr.account_id, mcr.organisationoverride_id, mcr.dbmodified
  • Index Cond: (mcr.account_id = p.account_id)
  • Filter: (mcr.main AND ((mcr.relationshiptype)::text = 'TECHNICAL'::text))
  • Rows Removed by Filter: 2
  • Worker 0: actual time=0.004..0.004 rows=1 loops=1,147
27. 11.650 11.650 ↑ 1.0 1 2,330 / 2

Index Scan using pricingplan_pkey on public.pricingplan (cost=0.29..5.24 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=2,330)

  • Output: pricingplan.id, pricingplan.description, pricingplan.monthsvalid, pricingplan.status, pricingplan.creationdate, pricingplan.activateddate, pricingplan.grandfathereddate, pricingplan.updated, pricingplan.updatedby, pricingplan.productpackage_id, pricingplan.maxnewquotedate, pricingplan.nextpricingplan_id, pricingplan.dbmodified, pricingplan.uuid, pricingplan.delegatingpricingplan, pricingplan.dunningpricingplan_id, pricingplan.license_pricing_plan_uuid, pricingplan.alternateperiodpricingplan_id, pricingplan.superseded, pricingplan.external_sku_id, pricingplan.skupackage_id
  • Index Cond: (pricingplan.id = a.pricingplan_id)
  • Worker 0: actual time=0.011..0.011 rows=1 loops=1,147
28. 2.330 2.330 ↑ 1.0 1 2,330 / 2

Index Scan using productfeatureusage_pkey on public.productfeatureusage pfu (cost=0.57..5.67 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=2,330)

  • Output: pfu.id, pfu.productfeature_id, pfu.unitcount, pfu.created, pfu.updated, pfu.updatedby, pfu.starter, pfu.dbmodified
  • Index Cond: (pfu.id = lp.productfeatureusage_id)
  • Worker 0: actual time=0.002..0.002 rows=1 loops=1,147
29. 0.681 1.196 ↓ 1.0 4,989 2 / 2

Hash (cost=101.45..101.45 rows=4,945 width=18) (actual time=1.196..1.196 rows=4,989 loops=2)

  • Output: pf.featurekey, pf.id
  • Buckets: 8,192 Batches: 1 Memory Usage: 338kB
  • Worker 0: actual time=1.172..1.172 rows=4,989 loops=1
30. 0.515 0.515 ↓ 1.0 4,989 2 / 2

Seq Scan on public.productfeature pf (cost=0.00..101.45 rows=4,945 width=18) (actual time=0.008..0.515 rows=4,989 loops=2)

  • Output: pf.featurekey, pf.id
  • Worker 0: actual time=0.011..0.511 rows=4,989 loops=1
31. 2.330 2.330 ↑ 1.0 1 2,330 / 2

Index Scan using contact_pkey on public.contact mc (cost=0.43..5.41 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=2,330)

  • Output: mc.id, mc.firstname, mc.lastname, mc.email, mc.phone, mc.netsuiteid, mc.organisation_id, mc.dbmodified, mc.smart_domain, mc.screenname, mc.department, mc.""position"", mc.homepageurl, mc.location, mc.aboutme, mc.avatartype, mc.avatarurl, mc.instantmessenger, mc.external_uuid, mc.status
  • Index Cond: (mc.id = mcr.contact_id)
  • Worker 0: actual time=0.002..0.002 rows=1 loops=1,147
Planning time : 3.479 ms
Execution time : 88.275 ms