explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0zg9

Settings
# exclusive inclusive rows x rows loops node
1. 2,794.994 4,049.741 ↓ 140.5 22,507,830 1

Nested Loop (cost=935.10..4,388.68 rows=160,228 width=78) (actual time=91.961..4,049.741 rows=22,507,830 loops=1)

  • Output: contract_product_materialized.product_id, cmp.company_id, crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.uom_type_id
  • Buffers: shared hit=69,674 read=528
  • I/O Timings: read=20.971
2. 3.814 10.704 ↓ 2.5 2,397 1

Bitmap Heap Scan on client_kronos.contract_product_materialized (cost=47.13..1,486.90 rows=977 width=64) (actual time=6.974..10.704 rows=2,397 loops=1)

  • Output: contract_product_materialized.contract_item_id, contract_product_materialized.contract_id, contract_product_materialized.product_id, contract_product_materialized.estimated_quantity, contract_product_materialized.uom_type_id, contract_product_materialized.rebate_deviated_value, contract_product_materialized.rebate_deviated_type, contract_product_materialized.inactive_override, contract_product_materialized.rebate_direct_value, contract_product_materialized.rebate_direct_type, contract_product_materialized.rebate_direct_decimal_model, contract_product_materialized.rebate_deviated_decimal_model, contract_product_materialized.won, contract_product_materialized.rebate_growth_type, contract_product_materialized.rebate_growth_decimal_model, contract_product_materialized.lift_quantity
  • Recheck Cond: (contract_product_materialized.contract_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid)
  • Heap Blocks: exact=47
  • Buffers: shared hit=2 read=149
  • I/O Timings: read=8.110
3. 6.890 6.890 ↓ 2.5 2,397 1

Bitmap Index Scan on contract_product_materialized_pkey (cost=0.00..46.88 rows=977 width=0) (actual time=6.890..6.890 rows=2,397 loops=1)

  • Index Cond: (contract_product_materialized.contract_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid)
  • Buffers: shared hit=2 read=102
  • I/O Timings: read=6.344
4. 1,155.458 1,244.043 ↓ 57.3 9,390 2,397

Materialize (cost=887.97..899.34 rows=164 width=46) (actual time=0.036..0.519 rows=9,390 loops=2,397)

  • Output: cav.contract_amend_version_id, crv.date_range, cmp.company_id
  • Buffers: shared hit=69,672 read=379
  • I/O Timings: read=12.861
5. 1.183 88.585 ↓ 57.3 9,390 1

Nested Loop (cost=887.97..898.52 rows=164 width=46) (actual time=84.975..88.585 rows=9,390 loops=1)

  • Output: cav.contract_amend_version_id, crv.date_range, cmp.company_id
  • Buffers: shared hit=69,672 read=379
  • I/O Timings: read=12.861
6. 0.004 0.039 ↑ 1.0 1 1

Nested Loop (cost=0.56..6.60 rows=1 width=30) (actual time=0.034..0.039 rows=1 loops=1)

  • Output: cav.contract_amend_version_id, crv.date_range
  • Inner Unique: true
  • Buffers: shared hit=6
7. 0.024 0.024 ↑ 1.0 1 1

Index Scan using contract_amend_version_pkey on client_kronos.contract_amend_version cav (cost=0.28..3.30 rows=1 width=32) (actual time=0.020..0.024 rows=1 loops=1)

  • Output: cav.contract_amend_version_id, cav.contract_renew_version_id, cav.contract_state, cav.contract_amend_version_parent_id, cav.contract_name, cav.contact_name, cav.contact_email, cav.contact_phone, cav.create_timestamp, cav.amend_version
  • Index Cond: (cav.contract_amend_version_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid)
  • Buffers: shared hit=3
8. 0.011 0.011 ↑ 1.0 1 1

Index Scan using contract_version_pkey on client_kronos.contract_renew_version crv (cost=0.28..3.30 rows=1 width=30) (actual time=0.011..0.011 rows=1 loops=1)

  • Output: crv.contract_renew_version_id, crv.contract_family_id, crv.date_range, crv.create_timestamp, crv.dead, crv.renew_version
  • Index Cond: (crv.contract_renew_version_id = cav.contract_renew_version_id)
  • Buffers: shared hit=3
9. 1.821 87.363 ↓ 57.3 9,390 1

Unique (cost=887.40..888.63 rows=164 width=36) (actual time=84.939..87.363 rows=9,390 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Buffers: shared hit=69,666 read=379
  • I/O Timings: read=12.861
10. 4.490 85.542 ↓ 62.8 10,305 1

Sort (cost=887.40..887.81 rows=164 width=36) (actual time=84.938..85.542 rows=10,305 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Sort Key: cmp.company_id, ct.internal_company_type
  • Sort Method: quicksort Memory: 1,190kB
  • Buffers: shared hit=69,666 read=379
  • I/O Timings: read=12.861
11. 3.352 81.052 ↓ 62.8 10,305 1

Hash Join (cost=360.03..881.37 rows=164 width=36) (actual time=0.205..81.052 rows=10,305 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Inner Unique: true
  • Hash Cond: (cmp.company_type_id = ct.company_type_id)
  • Buffers: shared hit=69,645 read=379
  • I/O Timings: read=12.861
12. 0.973 77.690 ↓ 62.8 10,305 1

Nested Loop (cost=358.98..878.98 rows=164 width=48) (actual time=0.184..77.690 rows=10,305 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, cmp.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=69,641 read=379
  • I/O Timings: read=12.861
13. 0.439 56.107 ↓ 58.6 10,305 1

Nested Loop (cost=358.70..818.72 rows=176 width=32) (actual time=0.171..56.107 rows=10,305 loops=1)

  • Output: cav_1.contract_amend_version_id, cagg.agg_company_id
  • Buffers: shared hit=38,687 read=379
  • I/O Timings: read=12.861
14. 1.373 35.220 ↓ 58.4 10,224 1

Nested Loop Left Join (cost=358.29..663.13 rows=175 width=48) (actual time=0.142..35.220 rows=10,224 loops=1)

  • Output: c.contractee_company_id, cav_1.contract_amend_version_id, cg.company_id
  • Buffers: shared hit=7,592 read=379
  • I/O Timings: read=12.861
15. 0.036 0.464 ↓ 19.0 19 1

Hash Right Join (cost=357.87..373.54 rows=1 width=48) (actual time=0.061..0.464 rows=19 loops=1)

  • Output: c.contractee_company_id, cav_1.contract_amend_version_id, groupings_list.grouping_id
  • Hash Cond: (groupings_list.original_grouping_id = c.contractee_grouping_id)
  • Buffers: shared hit=85
16. 0.408 0.408 ↑ 6.0 77 1

CTE Scan on groupings_list (cost=350.94..360.22 rows=464 width=52) (actual time=0.014..0.408 rows=77 loops=1)

  • Output: groupings_list.original_grouping_id, NULL::uuid, groupings_list.grouping_id, NULL::integer
  • Buffers: shared hit=76
17.          

CTE groupings_list

18. 0.061 0.354 ↑ 6.0 77 1

Recursive Union (cost=0.00..350.94 rows=464 width=52) (actual time=0.012..0.354 rows=77 loops=1)

  • Buffers: shared hit=76
19. 0.059 0.059 ↑ 1.0 34 1

Seq Scan on client_kronos.grouping_company pg (cost=0.00..19.34 rows=34 width=52) (actual time=0.011..0.059 rows=34 loops=1)

  • Output: pg.grouping_id, pg.parent_grouping_id, pg.grouping_id, 0
  • Buffers: shared hit=19
20. 0.075 0.234 ↑ 3.1 14 3

Hash Join (cost=11.05..32.23 rows=43 width=52) (actual time=0.045..0.078 rows=14 loops=3)

  • Output: gl.original_grouping_id, cg_1.parent_grouping_id, cg_1.grouping_id, (gl.level + 1)
  • Hash Cond: (cg_1.parent_grouping_id = gl.grouping_id)
  • Buffers: shared hit=57
21. 0.123 0.123 ↑ 1.0 29 3

Seq Scan on client_kronos.grouping_company cg_1 (cost=0.00..19.34 rows=29 width=32) (actual time=0.005..0.041 rows=29 loops=3)

  • Output: cg_1.grouping_id, cg_1.client_id, cg_1.grouping_description, cg_1.grouping_cd, cg_1.grouping_type, cg_1.parent_grouping_id, cg_1.active_ind, cg_1.company_type_id, cg_1.grouping_company_type
  • Filter: cg_1.active_ind
  • Rows Removed by Filter: 5
  • Buffers: shared hit=57
22. 0.018 0.036 ↑ 13.1 26 3

Hash (cost=6.80..6.80 rows=340 width=36) (actual time=0.012..0.012 rows=26 loops=3)

  • Output: gl.original_grouping_id, gl.level, gl.grouping_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
23. 0.018 0.018 ↑ 13.1 26 3

WorkTable Scan on groupings_list gl (cost=0.00..6.80 rows=340 width=36) (actual time=0.002..0.006 rows=26 loops=3)

  • Output: gl.original_grouping_id, gl.level, gl.grouping_id
24. 0.001 0.020 ↑ 1.0 1 1

Hash (cost=6.91..6.91 rows=1 width=48) (actual time=0.020..0.020 rows=1 loops=1)

  • Output: c.contractee_grouping_id, c.contractee_company_id, cav_1.contract_amend_version_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=9
25. 0.003 0.019 ↑ 1.0 1 1

Nested Loop (cost=0.84..6.91 rows=1 width=48) (actual time=0.018..0.019 rows=1 loops=1)

  • Output: c.contractee_grouping_id, c.contractee_company_id, cav_1.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=9
26. 0.003 0.008 ↑ 1.0 1 1

Nested Loop (cost=0.56..6.60 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)

  • Output: crv_1.contract_family_id, cav_1.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=6
27. 0.003 0.003 ↑ 1.0 1 1

Index Scan using contract_amend_version_pkey on client_kronos.contract_amend_version cav_1 (cost=0.28..3.30 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: cav_1.contract_amend_version_id, cav_1.contract_renew_version_id, cav_1.contract_state, cav_1.contract_amend_version_parent_id, cav_1.contract_name, cav_1.contact_name, cav_1.contact_email, cav_1.contact_phone, cav_1.create_timestamp, cav_1.amend_version
  • Index Cond: (cav_1.contract_amend_version_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid)
  • Buffers: shared hit=3
28. 0.002 0.002 ↑ 1.0 1 1

Index Scan using contract_version_pkey on client_kronos.contract_renew_version crv_1 (cost=0.28..3.30 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: crv_1.contract_renew_version_id, crv_1.contract_family_id, crv_1.date_range, crv_1.create_timestamp, crv_1.dead, crv_1.renew_version
  • Index Cond: (crv_1.contract_renew_version_id = cav_1.contract_renew_version_id)
  • Buffers: shared hit=3
29. 0.008 0.008 ↑ 1.0 1 1

Index Scan using contract_family_pkey on client_kronos.contract_family c (cost=0.28..0.31 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: c.contract_family_id, c.client_id, c.contractee_company_id, c.contractee_grouping_id, c.creator_id, c.contract_sequence, c.cost_basis_type, c.contract_type, c.contract_title, c.contract_template_id, c.payment_calendar_id, c.catch_all, c.accrual_quantity_source, c.import_contract_number, c.accrual_lumpsum_spread
  • Index Cond: (c.contract_family_id = crv_1.contract_family_id)
  • Buffers: shared hit=3
30. 33.383 33.383 ↑ 2.3 538 19

Index Only Scan using company_grouping_pkey on client_kronos.company_grouping cg (cost=0.41..277.08 rows=1,251 width=32) (actual time=0.143..1.757 rows=538 loops=19)

  • Output: cg.company_id, cg.grouping_id
  • Index Cond: (cg.grouping_id = groupings_list.grouping_id)
  • Heap Fetches: 0
  • Buffers: shared hit=7,507 read=379
  • I/O Timings: read=12.861
31. 20.448 20.448 ↑ 1.0 1 10,224

Index Only Scan using company_agg_pkey on client_kronos.company_agg cagg (cost=0.41..0.88 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=10,224)

  • Output: cagg.company_id, cagg.agg_company_id
  • Index Cond: (cagg.company_id = COALESCE(c.contractee_company_id, cg.company_id))
  • Heap Fetches: 0
  • Buffers: shared hit=31,095
32. 20.610 20.610 ↑ 1.0 1 10,305

Index Scan using company_pkey on client_kronos.company cmp (cost=0.29..0.34 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=10,305)

  • Output: cmp.company_id, cmp.client_id, cmp.company_name, cmp.company_number, cmp.company_type_id, cmp.active_ind, cmp.address_1, cmp.address_2, cmp.address_3, cmp.address_4, cmp.city, cmp.region, cmp.postal_code, cmp.country, cmp.company_agg_id, cmp.deduction_interval, cmp.alt_address_active, cmp.alt_address_1, cmp.alt_address_2, cmp.alt_address_3, cmp.alt_address_4, cmp.alt_city, cmp.alt_region, cmp.alt_postal_code, cmp.alt_country, cmp.atlas_company_id, cmp.email, cmp.tax_type_id, cmp.redistributor_ind, cmp.operator_request, cmp.early_payment_discount, cmp.vip, cmp.deduction_contact_email, cmp.is_unipro, cmp.is_golbon, cmp.is_ima, cmp.cross_ship_company_id, cmp.tpo_ind, cmp.auto_submit_eclaim, cmp.auto_approve_eclaim
  • Index Cond: (cmp.company_id = cagg.agg_company_id)
  • Buffers: shared hit=30,954
33. 0.003 0.010 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=20) (actual time=0.009..0.010 rows=2 loops=1)

  • Output: ct.internal_company_type, ct.company_type_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
34. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on client_kronos.company_type ct (cost=0.00..1.02 rows=2 width=20) (actual time=0.006..0.007 rows=2 loops=1)

  • Output: ct.internal_company_type, ct.company_type_id
  • Buffers: shared hit=1
Planning time : 3.584 ms
Execution time : 5,107.264 ms