explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Eu8n

Settings
# exclusive inclusive rows x rows loops node
1. 0.080 92.659 ↑ 155.8 30 1

Hash Join (cost=12,988.03..23,601.59 rows=4,673 width=48) (actual time=92.425..92.659 rows=30 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=2503 read=1
2. 0.057 50.502 ↑ 155.8 30 1

Hash Join (cost=10,091.93..20,576.40 rows=4,673 width=60) (actual time=50.289..50.502 rows=30 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=1933 read=1
3. 0.045 18.958 ↑ 155.8 30 1

Hash Join (cost=8,041.35..18,513.54 rows=4,673 width=84) (actual time=18.755..18.958 rows=30 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=1631 read=1
4. 0.045 16.655 ↑ 155.8 30 1

Hash Join (cost=7,682.55..18,142.47 rows=4,673 width=96) (actual time=16.460..16.655 rows=30 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=1400 read=1
5. 0.078 11.935 ↑ 155.8 30 1

Hash Join (cost=7,233.76..17,681.41 rows=4,673 width=64) (actual time=11.750..11.935 rows=30 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=1249 read=1
6. 0.008 4.837 ↑ 155.8 30 1

Nested Loop (cost=6,534.85..16,970.24 rows=4,673 width=32) (actual time=4.662..4.837 rows=30 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id
  • Inner Unique: true
  • Buffers: shared hit=964 read=1
7. 0.948 4.674 ↑ 128.2 31 1

HashAggregate (cost=6,534.43..6,574.16 rows=3,973 width=16) (actual time=4.635..4.674 rows=31 loops=1)

  • Output: claim_product.contract_item_id
  • Group Key: claim_product.contract_item_id
  • Buffers: shared hit=844 read=1
8. 3.440 3.726 ↑ 1.1 4,282 1

Bitmap Heap Scan on client_pinnacle.claim_product (cost=66.65..6,522.74 rows=4,673 width=16) (actual time=0.385..3.726 rows=4,282 loops=1)

  • Output: claim_product.contract_item_id
  • Recheck Cond: (claim_product.claim_id = 'b5ae9090-6883-4599-9412-99ee77c6c594'::uuid)
  • Heap Blocks: exact=826
  • Buffers: shared hit=844 read=1
9. 0.286 0.286 ↑ 1.1 4,282 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..65.48 rows=4,673 width=0) (actual time=0.286..0.286 rows=4,282 loops=1)

  • Index Cond: (claim_product.claim_id = 'b5ae9090-6883-4599-9412-99ee77c6c594'::uuid)
  • Buffers: shared hit=19
10. 0.155 0.155 ↑ 1.0 1 31

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

  • 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=120
11. 3.414 7.020 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=32) (actual time=7.020..7.020 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
12. 3.606 3.606 ↑ 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..3.606 rows=18,396 loops=1)

  • Output: contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id
  • Buffers: shared hit=285
13. 2.429 4.675 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=32) (actual time=4.675..4.675 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.246 2.246 ↑ 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.246 rows=13,235 loops=1)

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

Hash (cost=287.80..287.80 rows=5,680 width=20) (actual time=2.258..2.258 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.271 1.271 ↑ 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.025..1.271 rows=5,680 loops=1)

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

Hash (cost=1,885.15..1,885.15 rows=13,235 width=24) (actual time=31.487..31.487 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.063 28.980 ↑ 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=24.191..28.980 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. 10.333 26.917 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=24.189..26.917 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. 10.581 16.584 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=4.845..16.584 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.206 1.206 ↑ 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.016..1.206 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.450 4.797 ↑ 1.0 13,235 1

Hash (cost=283.35..283.35 rows=13,235 width=40) (actual time=4.797..4.797 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.347 2.347 ↑ 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..2.347 rows=13,235 loops=1)

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

Hash (cost=2,666.15..2,666.15 rows=18,396 width=24) (actual time=42.077..42.077 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.838 38.459 ↑ 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=31.659..38.459 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. 12.303 35.621 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=31.658..35.621 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. 12.956 23.318 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=8.569..23.318 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.857 1.857 ↑ 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.857 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.995 8.505 ↑ 1.0 18,396 1

Hash (cost=468.96..468.96 rows=18,396 width=40) (actual time=8.505..8.505 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.510 4.510 ↑ 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.510 rows=18,396 loops=1)

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