explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jRkg

Settings
# exclusive inclusive rows x rows loops node
1. 18.115 427.165 ↑ 1.0 26,424 1

Hash Left Join (cost=31,735.41..67,152.76 rows=27,537 width=48) (actual time=322.152..427.165 rows=26,424 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_item.contract_id = contract_amend_version.contract_amend_version_id)
  • Buffers: shared hit=36024, temp read=1851 written=1845
2. 51.645 291.097 ↑ 1.0 26,424 1

Hash Left Join (cost=25,089.00..59,439.29 rows=27,537 width=32) (actual time=204.125..291.097 rows=26,424 loops=1)

  • Output: claim_product.claim_product_id, contract_item.contract_id
  • Inner Unique: true
  • Hash Cond: (claim_product.contract_item_id = contract_item.contract_item_id)
  • Buffers: shared hit=34485, temp read=1851 written=1845
3. 39.377 42.733 ↑ 1.0 26,424 1

Bitmap Heap Scan on client_pinnacle.claim_product (cost=389.34..31,681.35 rows=27,537 width=32) (actual time=7.149..42.733 rows=26,424 loops=1)

  • Output: claim_product.claim_product_id, claim_product.contract_item_id
  • Recheck Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Heap Blocks: exact=20870
  • Buffers: shared hit=20975
4. 3.356 3.356 ↑ 1.0 26,424 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..382.46 rows=27,537 width=0) (actual time=3.356..3.356 rows=26,424 loops=1)

  • Index Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
5. 110.801 196.719 ↑ 1.0 381,407 1

Hash (cost=17,324.07..17,324.07 rows=381,407 width=32) (actual time=196.719..196.719 rows=381,407 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id
  • Buckets: 131072 Batches: 4 Memory Usage: 7008kB
  • Buffers: shared hit=13510, temp written=1814
6. 85.918 85.918 ↑ 1.0 381,407 1

Seq Scan on client_pinnacle.contract_item (cost=0.00..17,324.07 rows=381,407 width=32) (actual time=3.902..85.918 rows=381,407 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id
  • Buffers: shared hit=13510
7. 4.623 117.953 ↑ 1.0 18,396 1

Hash (cost=6,416.46..6,416.46 rows=18,396 width=36) (actual time=117.953..117.953 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
  • Buckets: 32768 Batches: 1 Memory Usage: 1550kB
  • Buffers: shared hit=1539
8. 8.758 113.330 ↑ 1.0 18,396 1

Hash Join (cost=5,754.27..6,416.46 rows=18,396 width=36) (actual time=80.723..113.330 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
9. 7.224 64.695 ↑ 1.0 18,396 1

Hash Join (cost=2,858.18..3,472.06 rows=18,396 width=28) (actual time=40.792..64.695 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
10. 5.920 23.864 ↑ 1.0 18,396 1

Hash Join (cost=807.59..1,373.17 rows=18,396 width=52) (actual time=7.137..23.864 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
11. 8.964 15.624 ↑ 1.0 18,396 1

Hash Join (cost=448.79..966.05 rows=18,396 width=64) (actual time=4.783..15.624 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
12. 1.936 1.936 ↑ 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.009..1.936 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
13. 2.447 4.724 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=32) (actual time=4.724..4.724 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
14. 2.277 2.277 ↑ 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.277 rows=13,235 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buffers: shared hit=151
15. 1.009 2.320 ↑ 1.0 5,680 1

Hash (cost=287.80..287.80 rows=5,680 width=20) (actual time=2.320..2.320 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
16. 1.311 1.311 ↑ 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.023..1.311 rows=5,680 loops=1)

  • Output: contract_family.contract_sequence, contract_family.contract_family_id
  • Buffers: shared hit=231
17. 2.534 33.607 ↑ 1.0 13,235 1

Hash (cost=1,885.15..1,885.15 rows=13,235 width=24) (actual time=33.607..33.607 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
18. 2.023 31.073 ↑ 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=26.136..31.073 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
19. 11.081 29.050 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=26.135..29.050 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
20. 11.551 17.969 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=5.239..17.969 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
21. 1.225 1.225 ↑ 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.009..1.225 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
22. 2.681 5.193 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=40) (actual time=5.193..5.193 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
23. 2.512 2.512 ↑ 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.512 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buffers: shared hit=151
24. 3.572 39.877 ↑ 1.0 18,396 1

Hash (cost=2,666.15..2,666.15 rows=18,396 width=24) (actual time=39.877..39.877 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
25. 2.743 36.305 ↑ 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=29.697..36.305 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
26. 11.825 33.562 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=29.696..33.562 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
27. 11.847 21.737 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=8.269..21.737 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
28. 1.685 1.685 ↑ 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.008..1.685 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
29. 3.901 8.205 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=40) (actual time=8.205..8.205 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
30. 4.304 4.304 ↑ 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.304 rows=18,396 loops=1)

  • Output: cav2.contract_amend_version_id, cav2.contract_renew_version_id, cav2.create_timestamp
  • Buffers: shared hit=285