explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tc3l

Settings
# exclusive inclusive rows x rows loops node
1. 0.140 232.069 ↑ 214.8 65 1

Hash Join (cost=38,474.05..57,486.91 rows=13,960 width=48) (actual time=143.422..232.069 rows=65 loops=1)

  • Output: contract_item.contract_item_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)
  • Inner Unique: true
  • Hash Cond: (contract_amend_version.contract_amend_version_id = contract_amend_version_view.contract_amend_version_id)
  • Buffers: shared hit=36024
2. 0.083 187.882 ↑ 214.8 65 1

Hash Join (cost=35,577.95..54,205.16 rows=13,960 width=60) (actual time=99.315..187.882 rows=65 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id, contract_amend_version.contract_amend_version_id, contract_family.contract_sequence, contract_renew_version_view.contract_renew_version
  • Inner Unique: true
  • Hash Cond: (contract_renew_version.contract_renew_version_id = contract_renew_version_view.contract_renew_version_id)
  • Buffers: shared hit=35454
3. 0.066 153.464 ↑ 214.8 65 1

Hash Join (cost=33,527.37..52,117.91 rows=13,960 width=84) (actual time=64.931..153.464 rows=65 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id, contract_renew_version.contract_renew_version_id, contract_family.contract_sequence
  • Inner Unique: true
  • Hash Cond: (contract_renew_version.contract_family_id = contract_family.contract_family_id)
  • Buffers: shared hit=35152
4. 0.068 151.069 ↑ 214.8 65 1

Hash Join (cost=33,168.57..51,722.45 rows=13,960 width=96) (actual time=62.567..151.069 rows=65 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id, contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Inner Unique: true
  • Hash Cond: (contract_amend_version.contract_renew_version_id = contract_renew_version.contract_renew_version_id)
  • Buffers: shared hit=34921
5. 0.107 145.963 ↑ 214.8 65 1

Hash Join (cost=32,719.78..51,237.00 rows=13,960 width=64) (actual time=57.493..145.963 rows=65 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id, contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Inner Unique: true
  • Hash Cond: (contract_item.contract_id = contract_amend_version.contract_amend_version_id)
  • Buffers: shared hit=34770
6. 52.612 138.956 ↑ 214.8 65 1

Hash Join (cost=32,020.87..50,501.44 rows=13,960 width=32) (actual time=50.520..138.956 rows=65 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id
  • Inner Unique: true
  • Hash Cond: (contract_item.contract_item_id = claim_product.contract_item_id)
  • Buffers: shared hit=34485
7. 40.383 40.383 ↑ 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=4.168..40.383 rows=381,407 loops=1)

  • 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
  • Buffers: shared hit=13510
8. 0.027 45.961 ↑ 185.1 65 1

Hash (cost=31,870.49..31,870.49 rows=12,030 width=16) (actual time=45.961..45.961 rows=65 loops=1)

  • Output: claim_product.contract_item_id
  • Buckets: 16384 Batches: 1 Memory Usage: 132kB
  • Buffers: shared hit=20975
9. 5.948 45.934 ↑ 185.1 65 1

HashAggregate (cost=31,750.19..31,870.49 rows=12,030 width=16) (actual time=45.860..45.934 rows=65 loops=1)

  • Output: claim_product.contract_item_id
  • Group Key: claim_product.contract_item_id
  • Buffers: shared hit=20975
10. 36.586 39.986 ↑ 1.0 26,424 1

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

  • Output: 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
11. 3.400 3.400 ↑ 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.400..3.400 rows=26,424 loops=1)

  • Index Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
12. 3.360 6.900 ↑ 1.0 18,396 1

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

  • Output: contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1406kB
  • Buffers: shared hit=285
13. 3.540 3.540 ↑ 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.012..3.540 rows=18,396 loops=1)

  • Output: contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Buffers: shared hit=285
14. 2.648 5.038 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=32) (actual time=5.037..5.038 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
15. 2.390 2.390 ↑ 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.008..2.390 rows=13,235 loops=1)

  • Output: contract_renew_version.contract_family_id, contract_renew_version.contract_renew_version_id
  • Buffers: shared hit=151
16. 0.981 2.329 ↑ 1.0 5,680 1

Hash (cost=287.80..287.80 rows=5,680 width=20) (actual time=2.329..2.329 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
17. 1.348 1.348 ↑ 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.030..1.348 rows=5,680 loops=1)

  • Output: contract_family.contract_sequence, contract_family.contract_family_id
  • Buffers: shared hit=231
18. 2.861 34.335 ↑ 1.0 13,235 1

Hash (cost=1,885.15..1,885.15 rows=13,235 width=24) (actual time=34.335..34.335 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
19. 2.085 31.474 ↑ 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.154..31.474 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
20. 11.333 29.389 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=26.153..29.389 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
21. 11.536 18.056 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=5.360..18.056 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
22. 1.213 1.213 ↑ 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.020..1.213 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
23. 2.666 5.307 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=40) (actual time=5.307..5.307 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
24. 2.641 2.641 ↑ 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.641 rows=13,235 loops=1)

  • Output: crv2.contract_renew_version_id, crv2.contract_family_id, crv2.create_timestamp
  • Buffers: shared hit=151
25. 4.308 44.047 ↑ 1.0 18,396 1

Hash (cost=2,666.15..2,666.15 rows=18,396 width=24) (actual time=44.047..44.047 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
26. 2.957 39.739 ↑ 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=32.092..39.739 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
27. 13.204 36.782 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=32.091..36.782 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
28. 13.500 23.578 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=8.295..23.578 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
29. 1.845 1.845 ↑ 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.007..1.845 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
30. 3.819 8.233 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=40) (actual time=8.233..8.233 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
31. 4.414 4.414 ↑ 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.414 rows=18,396 loops=1)

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