explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rc8Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 74.106 ↑ 26.5 4 1

Nested Loop (cost=4,795.66..5,305.62 rows=106 width=48) (actual time=71.103..74.106 rows=4 loops=1)

  • Output: claim_product.claim_product_id, (((((contract_family.contract_sequence)::text || '.R'::text) || ((count(crv2.contract_renew_version_id)))::text) || '.A'::text) || ((count(cav2.contract_amend_version_id)))::text)
  • Inner Unique: true
  • Buffers: shared hit=931
2. 0.010 74.080 ↑ 26.5 4 1

Nested Loop (cost=4,795.38..5,267.57 rows=106 width=48) (actual time=71.085..74.080 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_renew_version.contract_family_id, (count(crv2.contract_renew_version_id)), (count(cav2.contract_amend_version_id))
  • Inner Unique: true
  • Buffers: shared hit=919
3. 2.367 74.042 ↑ 26.5 4 1

Hash Join (cost=4,795.09..5,233.06 rows=106 width=64) (actual time=71.054..74.042 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_amend_version.contract_renew_version_id, (count(crv2.contract_renew_version_id)), crv1.contract_renew_version_id, (count(cav2.contract_amend_version_id))
  • Hash Cond: (cav1.contract_amend_version_id = contract_amend_version.contract_amend_version_id)
  • Buffers: shared hit=907
4. 13.593 39.249 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=34.602..39.249 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
5. 14.347 25.656 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=9.361..25.656 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
6. 2.017 2.017 ↑ 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.014..2.017 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
7. 4.447 9.292 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=40) (actual time=9.292..9.292 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
8. 4.845 4.845 ↑ 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.845 rows=18,396 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buffers: shared hit=285
9. 0.007 32.426 ↑ 26.5 4 1

Hash (cost=2,495.54..2,495.54 rows=106 width=88) (actual time=32.426..32.426 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id, (count(crv2.contract_renew_version_id)), crv1.contract_renew_version_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=337
10. 1.411 32.419 ↑ 26.5 4 1

Hash Join (cost=2,180.15..2,495.54 rows=106 width=88) (actual time=29.465..32.419 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id, (count(crv2.contract_renew_version_id)), crv1.contract_renew_version_id
  • Hash Cond: (crv1.contract_renew_version_id = contract_amend_version.contract_renew_version_id)
  • Buffers: shared hit=337
11. 11.446 30.920 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=28.166..30.920 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
12. 11.988 19.474 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=6.232..19.474 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
13. 1.303 1.303 ↑ 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.012..1.303 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
14. 3.177 6.183 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=40) (actual time=6.182..6.183 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
15. 3.006 3.006 ↑ 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.004..3.006 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buffers: shared hit=151
16. 0.004 0.088 ↑ 26.5 4 1

Hash (cost=558.37..558.37 rows=106 width=64) (actual time=0.088..0.088 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=35
17. 0.008 0.084 ↑ 26.5 4 1

Nested Loop (cost=1.14..558.37 rows=106 width=64) (actual time=0.058..0.084 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=35
18. 0.006 0.064 ↑ 26.5 4 1

Nested Loop (cost=0.85..525.89 rows=106 width=32) (actual time=0.045..0.064 rows=4 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id
  • Inner Unique: true
  • Buffers: shared hit=23
19. 0.034 0.034 ↑ 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.029..0.034 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
20. 0.024 0.024 ↑ 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.006..0.006 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: (contract_item.contract_item_id = claim_product.contract_item_id)
  • Buffers: shared hit=16
21. 0.012 0.012 ↑ 1.0 1 4

Index Scan using contract_amend_version_pkey on client_pinnacle.contract_amend_version (cost=0.29..0.31 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4)

  • 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
  • Index Cond: (contract_amend_version.contract_amend_version_id = contract_item.contract_id)
  • Buffers: shared hit=12
22. 0.028 0.028 ↑ 1.0 1 4

Index Scan using contract_version_pkey on client_pinnacle.contract_renew_version (cost=0.29..0.33 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=4)

  • Output: contract_renew_version.contract_renew_version_id, contract_renew_version.contract_family_id, contract_renew_version.date_range, contract_renew_version.create_timestamp, contract_renew_version.dead
  • Index Cond: (contract_renew_version.contract_renew_version_id = crv1.contract_renew_version_id)
  • Buffers: shared hit=12
23. 0.012 0.012 ↑ 1.0 1 4

Index Scan using contract_family_pkey on client_pinnacle.contract_family (cost=0.28..0.33 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=4)

  • Output: contract_family.contract_family_id, contract_family.client_id, contract_family.contractee_company_id, contract_family.contractee_grouping_id, contract_family.creator_id, contract_family.contract_sequence, contract_family.cost_basis_type, contract_family.contract_type, contract_family.contract_title, contract_family.contract_template_id, contract_family.payment_calendar_id, contract_family.catch_all, contract_family.import_contract_number, contract_family.accrual_quantity_source
  • Index Cond: (contract_family.contract_family_id = contract_renew_version.contract_family_id)
  • Buffers: shared hit=12