explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QpWYe

Settings
# exclusive inclusive rows x rows loops node
1. 1.461 100.260 ↑ 1.4 605 1

Unique (cost=75,812.89..75,871.69 rows=840 width=375) (actual time=98.665..100.260 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.740 98.799 ↓ 2.8 2,330 1

Sort (cost=75,812.89..75,814.99 rows=840 width=375) (actual time=98.664..98.799 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. 4.554 89.059 ↓ 2.8 2,330 1

Gather (cost=5,958.70..75,772.09 rows=840 width=375) (actual time=50.600..89.059 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.381 84.505 ↓ 2.4 1,165 2 / 2

Nested Loop Left Join (cost=4,958.70..74,686.36 rows=494 width=375) (actual time=47.657..84.505 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=45.846..82.821 rows=1,182 loops=1
5. 0.209 81.794 ↓ 2.4 1,165 2 / 2

Nested Loop Left Join (cost=4,958.26..72,009.51 rows=494 width=354) (actual time=47.644..81.794 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
  • Worker 0: actual time=45.835..80.120 rows=1,182 loops=1
6. 0.420 76.925 ↓ 2.4 1,165 2 / 2

Hash Left Join (cost=4,957.70..70,766.59 rows=494 width=346) (actual time=47.629..76.925 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
  • Inner Unique: true
  • Hash Cond: (pfu.productfeature_id = pf.id)
  • Worker 0: actual time=45.819..75.191 rows=1,182 loops=1
7. 1.068 75.314 ↓ 2.4 1,165 2 / 2

Nested Loop Left Join (cost=4,794.44..70,602.03 rows=494 width=336) (actual time=46.397..75.314 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
  • Inner Unique: true
  • Worker 0: actual time=44.582..73.568 rows=1,182 loops=1
8. 0.611 71.916 ↓ 2.4 1,165 2 / 2

Hash Left Join (cost=4,793.87..67,800.58 rows=494 width=331) (actual time=46.378..71.916 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
  • Inner Unique: true
  • Hash Cond: (a.pricingplan_id = pricingplan.id)
  • Worker 0: actual time=44.564..70.178 rows=1,182 loops=1
9. 0.322 58.419 ↓ 2.4 1,165 2 / 2

Hash Left Join (cost=2,579.55..65,584.96 rows=494 width=327) (actual time=33.237..58.419 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
  • Inner Unique: true
  • Hash Cond: (prodpack.parent_id = prodpackparent.id)
  • Worker 0: actual time=31.793..57.056 rows=1,182 loops=1
10. 0.440 54.798 ↓ 2.4 1,165 2 / 2

Hash Left Join (cost=2,120.99..65,125.10 rows=494 width=264) (actual time=29.894..54.798 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, prodpack.parent_id
  • Inner Unique: true
  • Hash Cond: (m.productpackage_id = prodpack.id)
  • Worker 0: actual time=29.225..54.212 rows=1,182 loops=1
11. 0.581 48.765 ↓ 2.4 1,165 2 / 2

Hash Join (cost=1,662.43..64,665.25 rows=494 width=174) (actual time=24.254..48.765 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, m.productpackage_id
  • Inner Unique: true
  • Hash Cond: (a.merchandise_id = m.id)
  • Worker 0: actual time=23.587..48.204 rows=1,182 loops=1
12. 0.770 24.741 ↓ 2.4 1,165 2 / 2

Nested Loop (cost=68.56..63,070.08 rows=494 width=89) (actual time=0.601..24.741 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
  • Join Filter: (p.account_id = slp.account_id)
  • Worker 0: actual time=0.083..24.334 rows=1,182 loops=1
13. 1.025 11.156 ↓ 2.4 1,165 2 / 2

Nested Loop (cost=67.99..24,004.40 rows=494 width=89) (actual time=0.584..11.156 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.062..10.570 rows=1,182 loops=1
14. 1.477 7.801 ↓ 2.4 1,165 2 / 2

Nested Loop (cost=67.42..21,093.75 rows=494 width=58) (actual time=0.572..7.801 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.049..7.222 rows=1,182 loops=1
15. 1.450 1.837 ↓ 1.6 2,244 2 / 2

Parallel Bitmap Heap Scan on public.period p (cost=66.98..9,371.36 rows=1,398 width=16) (actual time=0.556..1.837 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=977
  • Worker 0: actual time=0.032..1.306 rows=2,265 loops=1
16. 0.387 0.387 ↓ 2.0 4,647 1 / 2

Bitmap Index Scan on period_partner_id (cost=0.00..66.39 rows=2,376 width=0) (actual time=0.773..0.773 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,265
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,182
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.02 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,182
20. 12.925 23.443 ↑ 1.0 37,191 2 / 2

Hash (cost=1,128.94..1,128.94 rows=37,194 width=93) (actual time=23.443..23.443 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=23.141..23.141 rows=37,191 loops=1
21. 10.518 10.518 ↑ 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.009..10.518 rows=37,191 loops=2)

  • Output: m.description, m.licensetype, m.id, m.productpackage_id
  • Worker 0: actual time=0.011..10.442 rows=37,191 loops=1
22. 2.407 5.593 ↓ 1.0 5,937 2 / 2

Hash (cost=384.36..384.36 rows=5,936 width=106) (actual time=5.593..5.593 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=5.592..5.592 rows=5,937 loops=1
23. 3.186 3.186 ↓ 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.010..3.186 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.013..3.156 rows=5,937 loops=1
24. 1.647 3.299 ↓ 1.0 5,937 2 / 2

Hash (cost=384.36..384.36 rows=5,936 width=79) (actual time=3.299..3.299 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.525..2.525 rows=5,937 loops=1
25. 1.652 1.652 ↓ 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.005..1.652 rows=5,937 loops=2)

  • Output: prodpackparent.key, prodpackparent.description, prodpackparent.id
  • Worker 0: actual time=0.004..1.241 rows=5,937 loops=1
26. 5.387 12.886 ↓ 1.0 39,569 2 / 2

Hash (cost=1,720.81..1,720.81 rows=39,481 width=12) (actual time=12.886..12.886 rows=39,569 loops=2)

  • Output: pricingplan.monthsvalid, pricingplan.id
  • Buckets: 65,536 Batches: 1 Memory Usage: 2,367kB
  • Worker 0: actual time=12.533..12.533 rows=39,569 loops=1
27. 7.499 7.499 ↓ 1.0 39,569 2 / 2

Seq Scan on public.pricingplan (cost=0.00..1,720.81 rows=39,481 width=12) (actual time=0.010..7.499 rows=39,569 loops=2)

  • Output: pricingplan.monthsvalid, pricingplan.id
  • Worker 0: actual time=0.011..7.244 rows=39,569 loops=1
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,182
29. 0.671 1.191 ↓ 1.0 4,989 2 / 2

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

  • Output: pf.featurekey, pf.id
  • Buckets: 8,192 Batches: 1 Memory Usage: 338kB
  • Worker 0: actual time=1.198..1.198 rows=4,989 loops=1
30. 0.520 0.520 ↓ 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.009..0.520 rows=4,989 loops=2)

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

Index Scan using contactrelationship_account on public.contactrelationship mcr (cost=0.56..8.38 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 = a.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,182
32. 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,182
Planning time : 3.273 ms
Execution time : 100.634 ms