explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KQjV

Settings
# exclusive inclusive rows x rows loops node
1. 1.450 2,659.591 ↑ 1.4 604 1

Unique (cost=211,718.29..211,779.04 rows=838 width=376) (actual time=2,658.032..2,659.591 rows=604 loops=1)

2. 13.940 2,658.141 ↓ 2.8 2,328 1

Sort (cost=211,718.29..211,720.38 rows=838 width=376) (actual time=2,658.031..2,658.141 rows=2,328 loops=1)

  • Sort Key: (CASE WHEN (lp.partner_id = 1,652,472) THEN true ELSE false END) DESC, lp.created DESC, 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, 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 ((prodpack.key)::text = 'atlassian.cloud'::text) THEN (((SubPlan 1) > 0) AND ((SubPlan 2) = (SubPlan 3))) ELSE false END)
  • Sort Method: quicksort Memory: 1,109kB
3. 0.000 2,644.201 ↓ 2.8 2,328 1

Gather (cost=5,958.68..211,677.60 rows=838 width=376) (actual time=37.541..2,644.201 rows=2,328 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 0.442 89.941 ↓ 2.4 1,164 2 / 2

Nested Loop Left Join (cost=4,958.68..74,608.82 rows=493 width=377) (actual time=35.473..89.941 rows=1,164 loops=2)

5. 1.238 86.007 ↓ 2.4 1,164 2 / 2

Nested Loop Left Join (cost=4,958.24..71,941.52 rows=493 width=354) (actual time=35.460..86.007 rows=1,164 loops=2)

6. 0.632 78.949 ↓ 2.4 1,164 2 / 2

Hash Left Join (cost=4,957.68..70,701.75 rows=493 width=346) (actual time=35.445..78.949 rows=1,164 loops=2)

  • Hash Cond: (pfu.productfeature_id = pf.id)
7. 0.256 77.104 ↓ 2.4 1,164 2 / 2

Nested Loop Left Join (cost=4,794.42..70,537.19 rows=493 width=336) (actual time=34.195..77.104 rows=1,164 loops=2)

8. 0.828 72.192 ↓ 2.4 1,164 2 / 2

Hash Left Join (cost=4,793.85..67,742.99 rows=493 width=331) (actual time=34.180..72.192 rows=1,164 loops=2)

  • Hash Cond: (a.pricingplan_id = pricingplan.id)
9. 0.453 58.791 ↓ 2.4 1,164 2 / 2

Hash Left Join (cost=2,579.52..65,527.37 rows=493 width=327) (actual time=21.361..58.791 rows=1,164 loops=2)

  • Hash Cond: (prodpack.parent_id = prodpackparent.id)
10. 0.664 55.918 ↓ 2.4 1,164 2 / 2

Hash Left Join (cost=2,120.96..65,067.52 rows=493 width=264) (actual time=18.899..55.918 rows=1,164 loops=2)

  • Hash Cond: (m.productpackage_id = prodpack.id)
11. 0.930 51.800 ↓ 2.4 1,164 2 / 2

Hash Join (cost=1,662.40..64,607.66 rows=493 width=174) (actual time=15.407..51.800 rows=1,164 loops=2)

  • Hash Cond: (a.merchandise_id = m.id)
12. 0.997 36.231 ↓ 2.4 1,164 2 / 2

Nested Loop (cost=68.54..63,012.50 rows=493 width=89) (actual time=0.492..36.231 rows=1,164 loops=2)

13. 0.821 31.742 ↓ 2.4 1,164 2 / 2

Nested Loop (cost=67.97..60,109.21 rows=493 width=58) (actual time=0.484..31.742 rows=1,164 loops=2)

  • Join Filter: (p.account_id = slp.account_id)
14. 2.322 11.133 ↓ 2.4 1,164 2 / 2

Nested Loop (cost=67.40..21,067.43 rows=494 width=58) (actual time=0.473..11.133 rows=1,164 loops=2)

15. 1.772 2.097 ↓ 1.6 2,238 2 / 2

Parallel Bitmap Heap Scan on period p (cost=66.96..9,359.59 rows=1,396 width=16) (actual time=0.461..2.097 rows=2,238 loops=2)

  • Recheck Cond: (partner_id = 1,652,472)
  • Heap Blocks: exact=4
16. 0.325 0.325 ↓ 1.9 4,625 1 / 2

Bitmap Index Scan on period_partner_id (cost=0.00..66.37 rows=2,373 width=0) (actual time=0.650..0.650 rows=4,625 loops=1)

  • Index Cond: (partner_id = 1,652,472)
17. 6.714 6.714 ↑ 1.0 1 4,476 / 2

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

  • Index Cond: (id = p.account_id)
  • Filter: ((parent_id IS NULL) AND ((status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 0
18. 19.788 19.788 ↑ 5.0 1 2,328 / 2

Index Scan using period_account_id on period slp (cost=0.57..78.97 rows=5 width=16) (actual time=0.017..0.017 rows=1 loops=2,328)

  • Index Cond: (account_id = a.id)
  • Filter: (previousperiod_id IS NULL)
  • Rows Removed by Filter: 19
19. 3.492 3.492 ↑ 1.0 1 2,328 / 2

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

  • Index Cond: (id = a.latestperiod_id)
20. 8.349 14.639 ↑ 1.0 37,191 2 / 2

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,151kB
21. 6.290 6.290 ↑ 1.0 37,191 2 / 2

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

22. 1.583 3.454 ↓ 1.0 5,937 2 / 2

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 894kB
23. 1.871 1.871 ↓ 1.0 5,937 2 / 2

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

24. 1.282 2.420 ↓ 1.0 5,937 2 / 2

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 734kB
25. 1.138 1.138 ↓ 1.0 5,937 2 / 2

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

26. 5.285 12.573 ↓ 1.0 39,569 2 / 2

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,367kB
27. 7.288 7.288 ↓ 1.0 39,569 2 / 2

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

28. 4.656 4.656 ↑ 1.0 1 2,328 / 2

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

  • Index Cond: (id = lp.productfeatureusage_id)
29. 0.667 1.213 ↓ 1.0 4,989 2 / 2

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 338kB
30. 0.546 0.546 ↓ 1.0 4,989 2 / 2

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

31. 5.820 5.820 ↑ 4.0 1 2,328 / 2

Index Scan using contactrelationship_account on contactrelationship mcr (cost=0.56..8.37 rows=4 width=16) (actual time=0.005..0.005 rows=1 loops=2,328)

  • Index Cond: (account_id = a.id)
  • Filter: (main AND ((relationshiptype)::text = 'TECHNICAL'::text))
  • Rows Removed by Filter: 2
32. 3.492 3.492 ↑ 1.0 1 2,328 / 2

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

  • Index Cond: (id = mcr.contact_id)
33.          

SubPlan (for Gather)

34. 0.578 36.414 ↑ 1.0 1 578

Aggregate (cost=65.55..65.56 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=578)

35. 1.025 35.836 ↓ 1.7 5 578

Nested Loop Anti Join (cost=1.70..65.55 rows=3 width=0) (actual time=0.021..0.062 rows=5 loops=578)

36. 1.265 16.184 ↓ 1.7 5 578

Nested Loop (cost=1.13..37.11 rows=3 width=8) (actual time=0.011..0.028 rows=5 loops=578)

37. 6.936 6.936 ↓ 1.7 5 578

Index Scan using account_parent_id on account child (cost=0.56..19.35 rows=3 width=16) (actual time=0.005..0.012 rows=5 loops=578)

  • Index Cond: (parent_id = a.id)
  • Filter: ((status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 6
38. 7.983 7.983 ↑ 1.0 1 2,661

Index Only Scan using period_pkey on period childperiod (cost=0.57..5.92 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,661)

  • Index Cond: (id = child.latestperiod_id)
  • Heap Fetches: 2,087
39. 18.627 18.627 ↓ 0.0 0 2,661

Index Only Scan using accountproperty_unique_keys on accountproperty childproperty (cost=0.56..7.25 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=2,661)

  • Index Cond: ((key = 'account.deactivated'::text) AND (account_id = child.id))
  • Heap Fetches: 121
40. 0.578 24.854 ↑ 1.0 1 578

Aggregate (cost=65.55..65.56 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=578)

41. 1.145 24.276 ↓ 1.7 5 578

Nested Loop Anti Join (cost=1.70..65.55 rows=3 width=0) (actual time=0.013..0.042 rows=5 loops=578)

42. 0.458 9.826 ↓ 1.7 5 578

Nested Loop (cost=1.13..37.11 rows=3 width=8) (actual time=0.006..0.017 rows=5 loops=578)

43. 4.046 4.046 ↓ 1.7 5 578

Index Scan using account_parent_id on account child_1 (cost=0.56..19.35 rows=3 width=16) (actual time=0.003..0.007 rows=5 loops=578)

  • Index Cond: (parent_id = a.id)
  • Filter: ((status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 6
44. 5.322 5.322 ↑ 1.0 1 2,661

Index Only Scan using period_pkey on period childperiod_1 (cost=0.57..5.92 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,661)

  • Index Cond: (id = child_1.latestperiod_id)
  • Heap Fetches: 2,087
45. 13.305 13.305 ↓ 0.0 0 2,661

Index Only Scan using accountproperty_unique_keys on accountproperty childproperty_1 (cost=0.56..7.25 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=2,661)

  • Index Cond: ((key = 'account.deactivated'::text) AND (account_id = child_1.id))
  • Heap Fetches: 121
46. 0.000 2,537.420 ↑ 1.0 1 578

Aggregate (cost=31.12..31.13 rows=1 width=8) (actual time=4.390..4.390 rows=1 loops=578)

47. 0.530 2,537.420 ↓ 0.0 0 578

Nested Loop Anti Join (cost=1.70..31.12 rows=1 width=0) (actual time=4.377..4.390 rows=0 loops=578)

48. 0.000 2,536.842 ↓ 0.0 0 578

Nested Loop (cost=1.13..17.18 rows=1 width=8) (actual time=4.376..4.389 rows=0 loops=578)

49. 456.620 456.620 ↓ 2,020.0 2,020 578

Index Scan using migrationevaluation_id_unique_idx on period childperiod_2 (cost=0.57..8.59 rows=1 width=8) (actual time=0.005..0.790 rows=2,020 loops=578)

  • Index Cond: (migrationevaluation_id IS NOT NULL)
50. 2,335.120 2,335.120 ↓ 0.0 0 1,167,560

Index Scan using account_latestperiod on account child_2 (cost=0.56..8.59 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,167,560)

  • Index Cond: (latestperiod_id = childperiod_2.id)
  • Filter: ((parent_id = a.id) AND ((status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 0
51. 0.048 0.048 ↓ 0.0 0 6

Index Only Scan using accountproperty_unique_keys on accountproperty childproperty_2 (cost=0.56..7.25 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=6)

  • Index Cond: ((key = 'account.deactivated'::text) AND (account_id = child_2.id))
  • Heap Fetches: 0
Planning time : 4.333 ms
Execution time : 2,660.078 ms