explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sFvj

Settings
# exclusive inclusive rows x rows loops node
1. 1.990 112.405 ↑ 26.5 4 1

Hash Right Join (cost=6,281.49..7,085.36 rows=106 width=48) (actual time=103.698..112.405 rows=4 loops=1)

  • Output: claim_product.claim_product_id, (((((contract_family.contract_sequence)::text || '.R'::text) || (contract_renew_version_view.contract_renew_version)::text) || '.A'::text) || (contract_amend_version_view.contract_amend_version)::text)
  • Hash Cond: (contract_amend_version.contract_amend_version_id = contract_item.contract_id)
  • Buffers: shared hit=1562
2. 7.800 110.366 ↑ 1.0 18,396 1

Hash Join (cost=5,754.27..6,416.46 rows=18,396 width=36) (actual time=80.217..110.366 rows=18,396 loops=1)

  • Output: contract_family.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version.contract_amend_version_id, contract_amend_version_view.contract_amend_version
  • Inner Unique: true
  • Hash Cond: (contract_amend_version.contract_amend_version_id = contract_amend_version_view.contract_amend_version_id)
  • Buffers: shared hit=1539
3. 6.450 61.706 ↑ 1.0 18,396 1

Hash Join (cost=2,858.18..3,472.06 rows=18,396 width=28) (actual time=39.275..61.706 rows=18,396 loops=1)

  • Output: contract_family.contract_sequence, contract_renew_version_view.contract_renew_version, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Hash Cond: (contract_renew_version.contract_renew_version_id = contract_renew_version_view.contract_renew_version_id)
  • Buffers: shared hit=969
4. 5.717 22.874 ↑ 1.0 18,396 1

Hash Join (cost=807.59..1,373.17 rows=18,396 width=52) (actual time=6.857..22.874 rows=18,396 loops=1)

  • Output: contract_family.contract_sequence, contract_renew_version.contract_renew_version_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Hash Cond: (contract_renew_version.contract_family_id = contract_family.contract_family_id)
  • Buffers: shared hit=667
5. 8.412 14.857 ↑ 1.0 18,396 1

Hash Join (cost=448.79..966.05 rows=18,396 width=64) (actual time=4.522..14.857 rows=18,396 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Hash Cond: (contract_amend_version.contract_renew_version_id = contract_renew_version.contract_renew_version_id)
  • Buffers: shared hit=436
6. 1.969 1.969 ↑ 1.0 18,396 1

Seq Scan on client_pinnacle.contract_amend_version (cost=0.00..468.96 rows=18,396 width=32) (actual time=0.010..1.969 rows=18,396 loops=1)

  • Output: contract_amend_version.contract_amend_version_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_state, contract_amend_version.contract_amend_version_parent_id, contract_amend_version.contract_name, contract_amend_version.contact_name, contract_amend_version.contact_email, contract_amend_version.contact_phone, contract_amend_version.create_timestamp
  • Buffers: shared hit=285
7. 2.275 4.476 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=32) (actual time=4.476..4.476 rows=13,235 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buckets: 16384 Batches: 1 Memory Usage: 956kB
  • Buffers: shared hit=151
8. 2.201 2.201 ↑ 1.0 13,235 1

Seq Scan on client_pinnacle.contract_renew_version (cost=0.00..283.35 rows=13,235 width=32) (actual time=0.006..2.201 rows=13,235 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buffers: shared hit=151
9. 1.018 2.300 ↑ 1.0 5,680 1

Hash (cost=287.80..287.80 rows=5,680 width=20) (actual time=2.300..2.300 rows=5,680 loops=1)

  • Output: contract_family.contract_sequence, contract_family.contract_family_id
  • Buckets: 8192 Batches: 1 Memory Usage: 353kB
  • Buffers: shared hit=231
10. 1.282 1.282 ↑ 1.0 5,680 1

Seq Scan on client_pinnacle.contract_family (cost=0.00..287.80 rows=5,680 width=20) (actual time=0.026..1.282 rows=5,680 loops=1)

  • Output: contract_family.contract_sequence, contract_family.contract_family_id
  • Buffers: shared hit=231
11. 2.485 32.382 ↑ 1.0 13,235 1

Hash (cost=1,885.15..1,885.15 rows=13,235 width=24) (actual time=32.382..32.382 rows=13,235 loops=1)

  • Output: contract_renew_version_view.contract_renew_version, contract_renew_version_view.contract_renew_version_id
  • Buckets: 16384 Batches: 1 Memory Usage: 852kB
  • Buffers: shared hit=302
12. 2.027 29.897 ↑ 1.0 13,235 1

Subquery Scan on contract_renew_version_view (cost=1,620.45..1,885.15 rows=13,235 width=24) (actual time=25.194..29.897 rows=13,235 loops=1)

  • Output: contract_renew_version_view.contract_renew_version, contract_renew_version_view.contract_renew_version_id
  • Buffers: shared hit=302
13. 10.740 27.870 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=25.192..27.870 rows=13,235 loops=1)

  • Output: crv1.contract_renew_version_id, count(crv2.contract_renew_version_id)
  • Group Key: crv1.contract_renew_version_id
  • Buffers: shared hit=302
14. 11.046 17.130 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=4.949..17.130 rows=24,312 loops=1)

  • Output: crv1.contract_renew_version_id, crv2.contract_renew_version_id
  • Hash Cond: (crv1.contract_family_id = crv2.contract_family_id)
  • Join Filter: (crv2.create_timestamp < crv1.create_timestamp)
  • Rows Removed by Join Filter: 32009
  • Buffers: shared hit=302
15. 1.172 1.172 ↑ 1.0 13,235 1

Seq Scan on client_pinnacle.contract_renew_version crv1 (cost=0.00..283.35 rows=13,235 width=40) (actual time=0.006..1.172 rows=13,235 loops=1)

  • Output: crv1.contract_renew_version_id, crv1.contract_family_id, crv1.date_range, crv1.create_timestamp, crv1.dead
  • Buffers: shared hit=151
16. 2.600 4.912 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=40) (actual time=4.912..4.912 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buckets: 16384 Batches: 1 Memory Usage: 1059kB
  • Buffers: shared hit=151
17. 2.312 2.312 ↑ 1.0 13,235 1

Seq Scan on client_pinnacle.contract_renew_version crv2 (cost=0.00..283.35 rows=13,235 width=40) (actual time=0.003..2.312 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buffers: shared hit=151
18. 3.790 40.860 ↑ 1.0 18,396 1

Hash (cost=2,666.15..2,666.15 rows=18,396 width=24) (actual time=40.860..40.860 rows=18,396 loops=1)

  • Output: contract_amend_version_view.contract_amend_version, contract_amend_version_view.contract_amend_version_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1263kB
  • Buffers: shared hit=570
19. 2.721 37.070 ↑ 1.0 18,396 1

Subquery Scan on contract_amend_version_view (cost=2,298.23..2,666.15 rows=18,396 width=24) (actual time=30.149..37.070 rows=18,396 loops=1)

  • Output: contract_amend_version_view.contract_amend_version, contract_amend_version_view.contract_amend_version_id
  • Buffers: shared hit=570
20. 12.133 34.349 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=30.147..34.349 rows=18,396 loops=1)

  • Output: cav1.contract_amend_version_id, count(cav2.contract_amend_version_id)
  • Group Key: cav1.contract_amend_version_id
  • Buffers: shared hit=570
21. 12.192 22.216 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=8.375..22.216 rows=19,996 loops=1)

  • Output: cav1.contract_amend_version_id, cav2.contract_amend_version_id
  • Hash Cond: (cav1.contract_renew_version_id = cav2.contract_renew_version_id)
  • Join Filter: (cav2.create_timestamp < cav1.create_timestamp)
  • Rows Removed by Join Filter: 32398
  • Buffers: shared hit=570
22. 1.718 1.718 ↑ 1.0 18,396 1

Seq Scan on client_pinnacle.contract_amend_version cav1 (cost=0.00..468.96 rows=18,396 width=40) (actual time=0.011..1.718 rows=18,396 loops=1)

  • Output: cav1.contract_amend_version_id, cav1.contract_renew_version_id, cav1.contract_state, cav1.contract_amend_version_parent_id, cav1.contract_name, cav1.contact_name, cav1.contact_email, cav1.contact_phone, cav1.create_timestamp
  • Buffers: shared hit=285
23. 3.934 8.306 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=40) (actual time=8.306..8.306 rows=18,396 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buckets: 32768 Batches: 1 Memory Usage: 1550kB
  • Buffers: shared hit=285
24. 4.372 4.372 ↑ 1.0 18,396 1

Seq Scan on client_pinnacle.contract_amend_version cav2 (cost=0.00..468.96 rows=18,396 width=40) (actual time=0.003..4.372 rows=18,396 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buffers: shared hit=285
25. 0.005 0.049 ↑ 26.5 4 1

Hash (cost=525.89..525.89 rows=106 width=32) (actual time=0.049..0.049 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=23
26. 0.005 0.044 ↑ 26.5 4 1

Nested Loop Left Join (cost=0.85..525.89 rows=106 width=32) (actual time=0.025..0.044 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id
  • Inner Unique: true
  • Buffers: shared hit=23
27. 0.019 0.019 ↑ 26.5 4 1

Index Scan using claim_product_claim_id_idx on client_pinnacle.claim_product (cost=0.43..162.75 rows=106 width=32) (actual time=0.014..0.019 rows=4 loops=1)

  • Output: claim_product.claim_product_id, claim_product.contract_item_id
  • Index Cond: (claim_product.claim_id = '623ccf88-4f34-44d6-b1d5-4e795108d386'::uuid)
  • Buffers: shared hit=7
28. 0.020 0.020 ↑ 1.0 1 4

Index Scan using contract_item_pkey on client_pinnacle.contract_item (cost=0.42..3.43 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=4)

  • Output: contract_item.contract_item_id, contract_item.contract_id, contract_item.product_id, contract_item.grouping_id, contract_item.estimated_quantity, contract_item.uom_type_id, contract_item.rebate_deviated_value, contract_item.rebate_deviated_type, contract_item.inactive_override, contract_item.rebate_direct_value, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.won, contract_item.rebate_growth_type, contract_item.rebate_growth_decimal_model
  • Index Cond: (claim_product.contract_item_id = contract_item.contract_item_id)
  • Buffers: shared hit=16