explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AHLu : AccountOverview

Settings
# exclusive inclusive rows x rows loops node
1. 1.502 2,631.442 ↑ 1.4 604 1

Unique (cost=211,717.37..211,778.13 rows=838 width=376) (actual time=2,629.835..2,631.442 rows=604 loops=1)

2. 13.354 2,629.940 ↓ 2.8 2,328 1

Sort (cost=211,717.37..211,719.47 rows=838 width=376) (actual time=2,629.834..2,629.940 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,616.586 ↓ 2.8 2,328 1

Gather (cost=5,958.68..211,676.68 rows=838 width=376) (actual time=38.948..2,616.586 rows=2,328 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 0.277 74.111 ↓ 2.4 1,164 2 / 2

Nested Loop Left Join (cost=4,958.68..74,607.91 rows=493 width=377) (actual time=36.231..74.111 rows=1,164 loops=2)

5. 0.218 71.506 ↓ 2.4 1,164 2 / 2

Nested Loop Left Join (cost=4,958.24..71,940.67 rows=493 width=354) (actual time=36.218..71.506 rows=1,164 loops=2)

6. 0.420 66.632 ↓ 2.4 1,164 2 / 2

Hash Left Join (cost=4,957.68..70,700.92 rows=493 width=346) (actual time=36.204..66.632 rows=1,164 loops=2)

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

Nested Loop Left Join (cost=4,794.42..70,536.36 rows=493 width=336) (actual time=35.016..65.060 rows=1,164 loops=2)

8. 0.609 61.739 ↓ 2.4 1,164 2 / 2

Hash Left Join (cost=4,793.85..67,742.20 rows=493 width=331) (actual time=35.000..61.739 rows=1,164 loops=2)

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

Hash Left Join (cost=2,579.52..65,526.59 rows=493 width=327) (actual time=21.850..48.225 rows=1,164 loops=2)

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

Hash Left Join (cost=2,120.96..65,066.73 rows=493 width=264) (actual time=19.248..45.344 rows=1,164 loops=2)

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

Hash Join (cost=1,662.40..64,606.88 rows=493 width=174) (actual time=15.617..41.320 rows=1,164 loops=2)

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

Nested Loop (cost=68.54..63,011.72 rows=493 width=89) (actual time=0.518..25.822 rows=1,164 loops=2)

13. 0.674 22.636 ↓ 2.4 1,164 2 / 2

Nested Loop (cost=67.97..60,108.47 rows=493 width=58) (actual time=0.509..22.636 rows=1,164 loops=2)

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

Nested Loop (cost=67.40..21,067.42 rows=494 width=58) (actual time=0.496..7.994 rows=1,164 loops=2)

15. 1.351 1.700 ↓ 1.6 2,238 2 / 2

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

  • Recheck Cond: (partner_id = 1,652,472)
  • Heap Blocks: exact=4
16. 0.348 0.348 ↓ 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.697..0.697 rows=4,625 loops=1)

  • Index Cond: (partner_id = 1,652,472)
17. 4.476 4.476 ↑ 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.002..0.002 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. 13.968 13.968 ↑ 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.012..0.012 rows=1 loops=2,328)

  • Index Cond: (account_id = a.id)
  • Filter: (previousperiod_id IS NULL)
  • Rows Removed by Filter: 19
19. 2.328 2.328 ↑ 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.002..0.002 rows=1 loops=2,328)

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

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,151kB
21. 6.334 6.334 ↑ 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.006..6.334 rows=37,191 loops=2)

22. 1.610 3.590 ↓ 1.0 5,937 2 / 2

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 894kB
23. 1.980 1.980 ↓ 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.980 rows=5,937 loops=2)

24. 1.308 2.563 ↓ 1.0 5,937 2 / 2

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 734kB
25. 1.255 1.255 ↓ 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.255 rows=5,937 loops=2)

26. 5.577 12.905 ↓ 1.0 39,569 2 / 2

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,367kB
27. 7.328 7.328 ↓ 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.328 rows=39,569 loops=2)

28. 2.328 2.328 ↑ 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.002..0.002 rows=1 loops=2,328)

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

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

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

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

31. 4.656 4.656 ↑ 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.004..0.004 rows=1 loops=2,328)

  • Index Cond: (account_id = a.id)
  • Filter: (main AND ((relationshiptype)::text = 'TECHNICAL'::text))
  • Rows Removed by Filter: 2
32. 2.328 2.328 ↑ 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.002..0.002 rows=1 loops=2,328)

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

SubPlan (for Gather)

34. 0.578 27.744 ↑ 1.0 1 578

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

35. 1.723 27.166 ↓ 1.7 5 578

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

36. 1.614 12.138 ↓ 1.7 5 578

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

37. 5.202 5.202 ↓ 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.004..0.009 rows=5 loops=578)

  • Index Cond: (parent_id = a.id)
  • Filter: ((status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 6
38. 5.322 5.322 ↑ 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.002..0.002 rows=1 loops=2,661)

  • Index Cond: (id = child.latestperiod_id)
  • Heap Fetches: 2,087
39. 13.305 13.305 ↓ 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.005..0.005 rows=0 loops=2,661)

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

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

41. 1.494 20.230 ↓ 1.7 5 578

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

42. 1.963 8.092 ↓ 1.7 5 578

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

43. 3.468 3.468 ↓ 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.002..0.006 rows=5 loops=578)

  • Index Cond: (parent_id = a.id)
  • Filter: ((status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 6
44. 2.661 2.661 ↑ 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.001..0.001 rows=1 loops=2,661)

  • Index Cond: (id = child_1.latestperiod_id)
  • Heap Fetches: 2,087
45. 10.644 10.644 ↓ 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.004..0.004 rows=0 loops=2,661)

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

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

47. 0.536 2,522.392 ↓ 0.0 0 578

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

48. 0.000 2,521.814 ↓ 0.0 0 578

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

49. 464.712 464.712 ↓ 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.004..0.804 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.042 0.042 ↓ 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.007..0.007 rows=0 loops=6)

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