explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yGq4

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 386.989 ↑ 11,499.0 1 1

Hash Join (cost=65,413.14..66,466.58 rows=11,499 width=32) (actual time=382.972..386.989 rows=1 loops=1)

  • Output: (((((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, temp read=1842 written=1836
2. 0.050 318.294 ↑ 11,499.0 1 1

Hash Join (cost=62,517.04..63,252.81 rows=11,499 width=44) (actual time=314.277..318.294 rows=1 loops=1)

  • Output: contract_amend_version.contract_amend_version_id, contract_item.contract_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, temp read=1842 written=1836
3. 0.033 281.561 ↑ 11,499.0 1 1

Hash Join (cost=60,466.45..61,172.03 rows=11,499 width=68) (actual time=277.544..281.561 rows=1 loops=1)

  • Output: contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id, contract_item.contract_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, temp read=1842 written=1836
4. 0.035 279.209 ↑ 11,499.0 1 1

Hash Join (cost=60,107.65..60,783.02 rows=11,499 width=80) (actual time=275.192..279.209 rows=1 loops=1)

  • Output: contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id, contract_item.contract_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, temp read=1842 written=1836
5. 2.972 274.608 ↑ 11,499.0 1 1

Hash Join (cost=59,658.86..60,304.04 rows=11,499 width=48) (actual time=270.592..274.608 rows=1 loops=1)

  • Output: contract_amend_version.contract_renew_version_id, contract_amend_version.contract_amend_version_id, contract_item.contract_id
  • Inner Unique: true
  • Hash Cond: (contract_amend_version.contract_amend_version_id = contract_item.contract_id)
  • Buffers: shared hit=34770, temp read=1842 written=1836
6. 1.666 1.666 ↑ 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.006..1.666 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. 0.008 269.970 ↑ 11,499.0 1 1

Hash (cost=59,515.13..59,515.13 rows=11,499 width=16) (actual time=269.970..269.970 rows=1 loops=1)

  • Output: contract_item.contract_id
  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
  • Buffers: shared hit=34485, temp read=1842 written=1836
8. 5.354 269.962 ↑ 11,499.0 1 1

HashAggregate (cost=59,400.14..59,515.13 rows=11,499 width=16) (actual time=269.941..269.962 rows=1 loops=1)

  • Output: contract_item.contract_id
  • Group Key: contract_item.contract_id
  • Buffers: shared hit=34485, temp read=1842 written=1836
9. 48.012 264.608 ↑ 1.0 26,424 1

Hash Join (cost=25,089.00..59,331.29 rows=27,537 width=16) (actual time=182.714..264.608 rows=26,424 loops=1)

  • Output: 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=1842 written=1836
10. 37.677 41.124 ↑ 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.014..41.124 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.447 3.447 ↑ 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.447..3.447 rows=26,424 loops=1)

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

Hash (cost=17,324.07..17,324.07 rows=381,407 width=32) (actual time=175.471..175.472 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
13. 77.909 77.909 ↑ 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.879..77.909 rows=381,407 loops=1)

  • Output: contract_item.contract_item_id, contract_item.contract_id
  • Buffers: shared hit=13510
14. 2.355 4.566 ↑ 1.0 13,235 1

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

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

Hash (cost=287.80..287.80 rows=5,680 width=20) (actual time=2.319..2.319 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.313 1.313 ↑ 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.022..1.313 rows=5,680 loops=1)

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

Hash (cost=1,885.15..1,885.15 rows=13,235 width=24) (actual time=36.683..36.683 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.019 34.174 ↑ 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=29.563..34.174 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. 10.842 32.155 ↑ 1.0 13,235 1

HashAggregate (cost=1,620.45..1,752.80 rows=13,235 width=24) (actual time=29.562..32.155 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. 10.957 21.313 ↓ 1.8 24,312 1

Hash Left Join (cost=448.79..1,554.28 rows=13,235 width=32) (actual time=9.148..21.313 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.259 1.259 ↑ 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.017..1.259 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. 5.011 9.097 ↑ 1.0 13,235 1

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

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

Hash (cost=2,666.15..2,666.15 rows=18,396 width=24) (actual time=68.630..68.630 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. 4.726 61.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=49.772..61.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
27. 19.588 56.579 ↑ 1.0 18,396 1

HashAggregate (cost=2,298.23..2,482.19 rows=18,396 width=24) (actual time=49.770..56.579 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. 20.164 36.991 ↓ 1.1 19,996 1

Hash Left Join (cost=698.91..2,206.25 rows=18,396 width=32) (actual time=13.757..36.991 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. 3.144 3.144 ↑ 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..3.144 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. 6.891 13.683 ↑ 1.0 18,396 1

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

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