explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LyVH

Settings
# exclusive inclusive rows x rows loops node
1. 394.274 715.965 ↓ 0.0 0 1

Update on contract_item (cost=8,965.93..9,062.52 rows=2,350 width=296) (actual time=715.965..715.965 rows=0 loops=1)

2. 2.558 321.691 ↑ 1.2 1,948 1

Nested Loop (cost=8,965.93..9,062.52 rows=2,350 width=296) (actual time=252.154..321.691 rows=1,948 loops=1)

3. 4.156 254.159 ↓ 14.4 1,666 1

HashAggregate (cost=8,965.51..8,966.67 rows=116 width=182) (actual time=252.135..254.159 rows=1,666 loops=1)

  • Group Key: contract_amend_version.contract_amend_version_id
4. 1.915 250.003 ↓ 14.4 1,666 1

Nested Loop Anti Join (cost=1,479.84..8,965.22 rows=116 width=182) (actual time=53.524..250.003 rows=1,666 loops=1)

5. 2.452 200.676 ↓ 8.7 1,756 1

Nested Loop Semi Join (cost=1,478.59..7,880.59 rows=201 width=162) (actual time=53.499..200.676 rows=1,756 loops=1)

6. 3.771 144.092 ↓ 8.7 4,164 1

Nested Loop Left Join (cost=1,477.35..5,304.51 rows=478 width=138) (actual time=53.454..144.092 rows=4,164 loops=1)

7. 2.328 123.665 ↓ 8.7 4,164 1

Nested Loop Left Join (cost=1,473.92..3,648.24 rows=478 width=138) (actual time=53.442..123.665 rows=4,164 loops=1)

8. 3.161 104.681 ↓ 8.7 4,164 1

Nested Loop (cost=1,470.61..2,051.72 rows=478 width=138) (actual time=53.419..104.681 rows=4,164 loops=1)

9. 3.379 89.028 ↓ 8.7 4,164 1

Nested Loop (cost=1,470.32..1,882.12 rows=478 width=128) (actual time=53.410..89.028 rows=4,164 loops=1)

10. 1.174 73.157 ↓ 8.7 4,164 1

Nested Loop (cost=1,470.03..1,716.98 rows=478 width=98) (actual time=53.400..73.157 rows=4,164 loops=1)

11. 4.707 55.327 ↓ 8.7 4,164 1

HashAggregate (cost=1,469.62..1,474.40 rows=478 width=52) (actual time=53.382..55.327 rows=4,164 loops=1)

  • Group Key: cav.contract_amend_version_id
12. 2.150 50.620 ↓ 8.7 4,164 1

Nested Loop (cost=70.18..1,468.42 rows=478 width=52) (actual time=0.682..50.620 rows=4,164 loops=1)

13. 3.692 35.990 ↓ 9.0 4,160 1

Nested Loop (cost=69.77..1,227.28 rows=461 width=46) (actual time=0.670..35.990 rows=4,160 loops=1)

14. 4.499 23.988 ↓ 9.1 4,155 1

Hash Join (cost=69.48..1,063.35 rows=459 width=40) (actual time=0.653..23.988 rows=4,155 loops=1)

  • Hash Cond: (COALESCE(cmp_2.company_type_id, gc.company_type_id) = ct_2.company_type_id)
15. 8.326 19.466 ↑ 1.0 22,955 1

Hash Left Join (cost=68.33..1,000.66 rows=22,955 width=66) (actual time=0.615..19.466 rows=22,955 loops=1)

  • Hash Cond: (c.contractee_company_id = cmp_2.company_id)
16. 4.436 10.549 ↑ 1.0 22,955 1

Hash Left Join (cost=21.02..892.83 rows=22,955 width=60) (actual time=0.015..10.549 rows=22,955 loops=1)

  • Hash Cond: (c.contractee_grouping_id = gc.grouping_id)
17. 6.111 6.111 ↑ 1.0 22,955 1

Seq Scan on contract_family c (cost=0.00..811.55 rows=22,955 width=54) (actual time=0.005..6.111 rows=22,955 loops=1)

18. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=14.90..14.90 rows=490 width=38) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on grouping_company gc (cost=0.00..14.90 rows=490 width=38) (actual time=0.001..0.002 rows=0 loops=1)

20. 0.288 0.591 ↑ 1.0 947 1

Hash (cost=35.47..35.47 rows=947 width=38) (actual time=0.591..0.591 rows=947 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
21. 0.303 0.303 ↑ 1.0 947 1

Seq Scan on company cmp_2 (cost=0.00..35.47 rows=947 width=38) (actual time=0.004..0.303 rows=947 loops=1)

22. 0.005 0.023 ↑ 1.0 4 1

Hash (cost=1.10..1.10 rows=4 width=22) (actual time=0.023..0.023 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.018 0.018 ↑ 1.0 4 1

Seq Scan on company_type ct_2 (cost=0.00..1.10 rows=4 width=22) (actual time=0.015..0.018 rows=4 loops=1)

  • Filter: (internal_company_type = 'LOCATION'::enum.internal_company_type)
  • Rows Removed by Filter: 4
24. 8.310 8.310 ↑ 1.0 1 4,155

Index Scan using contract_renew_version_contract_family_id_idx on contract_renew_version crv (cost=0.29..0.35 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=4,155)

  • Index Cond: (contract_family_id = c.contract_family_id)
25. 12.480 12.480 ↑ 1.0 1 4,160

Index Scan using contract_amend_version_contract_renew_version_id_idx1 on contract_amend_version cav (cost=0.41..0.51 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=4,160)

  • Index Cond: (contract_renew_version_id = crv.contract_renew_version_id)
26. 16.656 16.656 ↑ 1.0 1 4,164

Index Scan using contract_amend_version_pkey on contract_amend_version (cost=0.41..0.51 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=4,164)

  • Index Cond: (contract_amend_version_id = cav.contract_amend_version_id)
27. 12.492 12.492 ↑ 1.0 1 4,164

Index Scan using contract_renew_version_pkey on contract_renew_version (cost=0.29..0.35 rows=1 width=46) (actual time=0.003..0.003 rows=1 loops=4,164)

  • Index Cond: (contract_renew_version_id = contract_amend_version.contract_renew_version_id)
28. 12.492 12.492 ↑ 1.0 1 4,164

Index Scan using contract_family_pkey on contract_family (cost=0.29..0.35 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=4,164)

  • Index Cond: (contract_family_id = contract_renew_version.contract_family_id)
29. 4.164 16.656 ↑ 1.0 1 4,164

Subquery Scan on crv2 (cost=3.31..3.33 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=4,164)

30. 0.000 12.492 ↑ 1.0 1 4,164

Aggregate (cost=3.31..3.32 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=4,164)

31. 12.492 12.492 ↓ 0.0 0 4,164

Index Scan using contract_renew_version_contract_family_id_idx on contract_renew_version crv2_1 (cost=0.29..3.31 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=4,164)

  • Index Cond: (contract_renew_version.contract_family_id = contract_family_id)
  • Filter: (create_timestamp < contract_renew_version.create_timestamp)
  • Rows Removed by Filter: 1
32. 0.000 16.656 ↑ 1.0 1 4,164

Subquery Scan on cav2 (cost=3.44..3.46 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=4,164)

33. 4.164 16.656 ↑ 1.0 1 4,164

Aggregate (cost=3.44..3.45 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=4,164)

34. 12.492 12.492 ↓ 0.0 0 4,164

Index Scan using contract_amend_version_contract_renew_version_id_idx1 on contract_amend_version cav2_1 (cost=0.41..3.43 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=4,164)

  • Index Cond: (contract_amend_version.contract_renew_version_id = contract_renew_version_id)
  • Filter: (create_timestamp < contract_amend_version.create_timestamp)
  • Rows Removed by Filter: 1
35. 4.039 54.132 ↓ 0.0 0 4,164

Nested Loop (cost=1.24..6.48 rows=3 width=28) (actual time=0.013..0.013 rows=0 loops=4,164)

36. 3.914 45.804 ↑ 6.0 1 4,164

Nested Loop (cost=1.11..5.54 rows=6 width=38) (actual time=0.008..0.011 rows=1 loops=4,164)

37. 0.000 33.312 ↑ 6.0 1 4,164

Nested Loop (cost=0.83..3.78 rows=6 width=32) (actual time=0.006..0.008 rows=1 loops=4,164)

38. 16.656 16.656 ↑ 6.0 1 4,164

Index Scan using idx_claim_contract_sequence on claim cl (cost=0.42..0.80 rows=6 width=26) (actual time=0.003..0.004 rows=1 loops=4,164)

  • Index Cond: (contract_sequence = contract_family.contract_sequence)
39. 17.156 17.156 ↑ 1.0 1 4,289

Index Scan using invoice_pkey on invoice inv (cost=0.42..0.50 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=4,289)

  • Index Cond: (invoice_id = cl.invoice_id)
40. 8.578 8.578 ↑ 1.0 1 4,289

Index Scan using company_pkey on company cmp (cost=0.28..0.29 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=4,289)

  • Index Cond: (company_id = inv.claimant_company_id)
41. 4.289 4.289 ↓ 0.0 0 4,289

Index Scan using company_type_pkey on company_type ct (cost=0.13..0.16 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=4,289)

  • Index Cond: (company_type_id = cmp.company_type_id)
  • Filter: (internal_company_type = 'DISTRIBUTOR'::enum.internal_company_type)
  • Rows Removed by Filter: 1
42. 1.890 47.412 ↓ 0.0 0 1,756

Nested Loop (cost=1.24..6.48 rows=3 width=28) (actual time=0.027..0.027 rows=0 loops=1,756)

43. 2.024 40.388 ↑ 2.0 3 1,756

Nested Loop (cost=1.11..5.54 rows=6 width=38) (actual time=0.008..0.023 rows=3 loops=1,756)

44. 3.914 28.096 ↑ 2.0 3 1,756

Nested Loop (cost=0.83..3.78 rows=6 width=32) (actual time=0.006..0.016 rows=3 loops=1,756)

45. 8.780 8.780 ↑ 2.0 3 1,756

Index Scan using idx_claim_contract_sequence on claim cl_1 (cost=0.42..0.80 rows=6 width=26) (actual time=0.002..0.005 rows=3 loops=1,756)

  • Index Cond: (contract_sequence = contract_family.contract_sequence)
46. 15.402 15.402 ↑ 1.0 1 5,134

Index Scan using invoice_pkey on invoice inv_1 (cost=0.42..0.50 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=5,134)

  • Index Cond: (invoice_id = cl_1.invoice_id)
47. 10.268 10.268 ↑ 1.0 1 5,134

Index Scan using company_pkey on company cmp_1 (cost=0.28..0.29 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=5,134)

  • Index Cond: (company_id = inv_1.claimant_company_id)
48. 5.134 5.134 ↓ 0.0 0 5,134

Index Scan using company_type_pkey on company_type ct_1 (cost=0.13..0.16 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=5,134)

  • Index Cond: (company_type_id = cmp_1.company_type_id)
  • Filter: (internal_company_type = 'LOCATION'::enum.internal_company_type)
  • Rows Removed by Filter: 1
49. 64.974 64.974 ↑ 5.0 1 1,666

Index Scan using contract_item_contract_id_product_id_key on contract_item (cost=0.42..0.78 rows=5 width=106) (actual time=0.036..0.039 rows=1 loops=1,666)

  • Index Cond: (contract_id = cav.contract_amend_version_id)