explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RI8L

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 130.285 ↑ 4.0 1 1

Nested Loop Left Join (cost=1,808.26..1,814.38 rows=4 width=80) (actual time=130.283..130.285 rows=1 loops=1)

  • Output: ci.contract_item_id, COALESCE(base.quantity, '0'::numeric), COALESCE(obj.quantity, '0'::numeric)
  • Join Filter: (obj.contract_item_id = ci.contract_item_id)
  • Buffers: shared hit=140,057 read=3
  • I/O Timings: read=0.775
2.          

CTE obj

3. 0.002 63.687 ↓ 0.0 0 1

GroupAggregate (cost=903.10..903.16 rows=1 width=48) (actual time=63.687..63.687 rows=0 loops=1)

  • Output: contract_product_materialized.contract_item_id, sum((((fi.quantity * (tuomc.rate / suomc.rate)) / ((upper(fi.date_range) - lower(fi.date_range)))::numeric) * ((upper((fi.date_range * crv.date_range)) - lower((fi.date_range * crv.date_range))))::numeric))
  • Group Key: contract_product_materialized.contract_item_id
  • Buffers: shared hit=70,028
4. 0.008 63.685 ↓ 0.0 0 1

Sort (cost=903.10..903.11 rows=1 width=104) (actual time=63.685..63.685 rows=0 loops=1)

  • Output: contract_product_materialized.contract_item_id, fi.quantity, tuomc.rate, suomc.rate, fi.date_range, crv.date_range
  • Sort Key: contract_product_materialized.contract_item_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=70,028
5. 0.002 63.677 ↓ 0.0 0 1

Nested Loop (cost=895.71..903.09 rows=1 width=104) (actual time=63.677..63.677 rows=0 loops=1)

  • Output: contract_product_materialized.contract_item_id, fi.quantity, tuomc.rate, suomc.rate, fi.date_range, crv.date_range
  • Inner Unique: true
  • Join Filter: ((fi.product_id = tuomc.product_id) AND (contract_product_materialized.uom_type_id = tuomc.uom_type_id))
  • Buffers: shared hit=70,028
6. 0.000 63.675 ↓ 0.0 0 1

Nested Loop (cost=895.43..902.69 rows=1 width=179) (actual time=63.675..63.675 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.product_id, crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.product_id, contract_product_materialized.uom_type_id, pro.product_id, suomc.rate, suomc.product_id
  • Inner Unique: true
  • Join Filter: (fi.product_id = contract_product_materialized.product_id)
  • Buffers: shared hit=70,028
7. 0.002 63.675 ↓ 0.0 0 1

Nested Loop (cost=894.87..899.20 rows=1 width=147) (actual time=63.675..63.675 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.product_id, crv.date_range, cav.contract_amend_version_id, pro.product_id, suomc.rate, suomc.product_id
  • Inner Unique: true
  • Join Filter: ((fi.product_id = suomc.product_id) AND (fi.uom_type_id = suomc.uom_type_id))
  • Buffers: shared hit=70,028
8. 0.000 63.673 ↓ 0.0 0 1

Nested Loop (cost=894.59..898.29 rows=2 width=142) (actual time=63.673..63.673 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.product_id, fi.uom_type_id, crv.date_range, cav.contract_amend_version_id, pro.product_id
  • Inner Unique: true
  • Buffers: shared hit=70,028
9. 0.004 63.673 ↓ 0.0 0 1

Merge Join (cost=894.31..897.63 rows=2 width=126) (actual time=63.673..63.673 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.product_id, fi.uom_type_id, crv.date_range, cav.contract_amend_version_id
  • Merge Cond: (cmp.company_id = fi.company_id)
  • Buffers: shared hit=70,028
10. 0.001 63.625 ↑ 164.0 1 1

Unique (cost=887.40..888.63 rows=164 width=36) (actual time=63.625..63.625 rows=1 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Buffers: shared hit=70,021
11. 4.161 63.624 ↑ 164.0 1 1

Sort (cost=887.40..887.81 rows=164 width=36) (actual time=63.624..63.624 rows=1 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=70,021
12. 2.723 59.463 ↓ 62.8 10,305 1

Hash Join (cost=360.03..881.37 rows=164 width=36) (actual time=0.121..59.463 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=70,021
13. 8.180 56.729 ↓ 62.8 10,305 1

Nested Loop (cost=358.98..878.98 rows=164 width=48) (actual time=0.102..56.729 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=70,020
14. 8.244 38.244 ↓ 58.6 10,305 1

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

  • Output: cav_1.contract_amend_version_id, cagg.agg_company_id
  • Buffers: shared hit=39,066
15. 1.314 19.776 ↓ 58.4 10,224 1

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

  • Output: c.contractee_company_id, cav_1.contract_amend_version_id, cg_1.company_id
  • Buffers: shared hit=7,971
16. 0.032 0.412 ↓ 19.0 19 1

Hash Right Join (cost=357.87..373.54 rows=1 width=48) (actual time=0.064..0.412 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
17. 0.345 0.345 ↑ 6.0 77 1

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

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

CTE groupings_list

19. 0.050 0.281 ↑ 6.0 77 1

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

  • Buffers: shared hit=76
20. 0.027 0.027 ↑ 1.0 34 1

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

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

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

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

Seq Scan on client_kronos.grouping_company cg (cost=0.00..19.34 rows=29 width=32) (actual time=0.004..0.031 rows=29 loops=3)

  • Output: cg.grouping_id, cg.client_id, cg.grouping_description, cg.grouping_cd, cg.grouping_type, cg.parent_grouping_id, cg.active_ind, cg.company_type_id, cg.grouping_company_type
  • Filter: cg.active_ind
  • Rows Removed by Filter: 5
  • Buffers: shared hit=57
23. 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
24. 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.003..0.006 rows=26 loops=3)

  • Output: gl.original_grouping_id, gl.level, gl.grouping_id
25. 0.002 0.035 ↑ 1.0 1 1

Hash (cost=6.91..6.91 rows=1 width=48) (actual time=0.035..0.035 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
26. 0.002 0.033 ↑ 1.0 1 1

Nested Loop (cost=0.84..6.91 rows=1 width=48) (actual time=0.033..0.033 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
27. 0.003 0.023 ↑ 1.0 1 1

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

  • Output: crv_1.contract_family_id, cav_1.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=6
28. 0.013 0.013 ↑ 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.012..0.013 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
29. 0.007 0.007 ↑ 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.007..0.007 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
30. 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
31. 18.050 18.050 ↑ 2.3 538 19

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

  • Output: cg_1.company_id, cg_1.grouping_id
  • Index Cond: (cg_1.grouping_id = groupings_list.grouping_id)
  • Heap Fetches: 0
  • Buffers: shared hit=7,886
32. 10.224 10.224 ↑ 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.001 rows=1 loops=10,224)

  • Output: cagg.company_id, cagg.agg_company_id
  • Index Cond: (cagg.company_id = COALESCE(c.contractee_company_id, cg_1.company_id))
  • Heap Fetches: 0
  • Buffers: shared hit=31,095
33. 10.305 10.305 ↑ 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.001..0.001 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
34. 0.002 0.011 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=20) (actual time=0.010..0.011 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
35. 0.009 0.009 ↑ 1.0 2 1

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

  • Output: ct.internal_company_type, ct.company_type_id
  • Buffers: shared hit=1
36. 0.006 0.044 ↓ 0.0 0 1

Sort (cost=6.91..6.92 rows=3 width=142) (actual time=0.044..0.044 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.company_id, fi.product_id, fi.uom_type_id, crv.date_range, cav.contract_amend_version_id
  • Sort Key: fi.company_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=7
37. 0.003 0.038 ↓ 0.0 0 1

Nested Loop (cost=0.71..6.88 rows=3 width=142) (actual time=0.038..0.038 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.company_id, fi.product_id, fi.uom_type_id, crv.date_range, cav.contract_amend_version_id
  • Buffers: shared hit=7
38. 0.003 0.024 ↑ 1.0 1 1

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

  • Output: cav.contract_amend_version_id, crv.date_range
  • Inner Unique: true
  • Buffers: shared hit=6
39. 0.012 0.012 ↑ 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.011..0.012 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
40. 0.009 0.009 ↑ 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.009..0.009 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
41. 0.011 0.011 ↓ 0.0 0 1

Index Scan using forecast_objective_exclusion on client_kronos.forecast_objective fi (cost=0.14..0.23 rows=5 width=112) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: fi.forecast_objective_id, fi.product_id, fi.company_id, fi.date_range, fi.uom_type_id, fi.quantity
  • Index Cond: (fi.date_range && crv.date_range)
  • Buffers: shared hit=1
42. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_pkey on client_kronos.product pro (cost=0.28..0.33 rows=1 width=16) (never executed)

  • Output: pro.product_id
  • Index Cond: (pro.product_id = fi.product_id)
  • Heap Fetches: 0
43. 0.000 0.000 ↓ 0.0 0

Index Scan using product_uom_conversion_pkey on client_kronos.product_uom_conversion suomc (cost=0.28..0.41 rows=3 width=37) (never executed)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Index Cond: (suomc.product_id = pro.product_id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using contract_product_materialized_pkey on client_kronos.contract_product_materialized (cost=0.56..3.47 rows=1 width=64) (never executed)

  • 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
  • Index Cond: ((contract_product_materialized.contract_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid) AND (contract_product_materialized.product_id = suomc.product_id))
45. 0.000 0.000 ↓ 0.0 0

Index Scan using product_uom_conversion_pkey on client_kronos.product_uom_conversion tuomc (cost=0.28..0.36 rows=3 width=37) (never executed)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Index Cond: (tuomc.product_id = suomc.product_id)
46.          

CTE base

47. 0.002 66.216 ↓ 0.0 0 1

GroupAggregate (cost=903.10..903.16 rows=1 width=48) (actual time=66.216..66.216 rows=0 loops=1)

  • Output: contract_product_materialized_1.contract_item_id, sum((((fi_1.quantity * (tuomc_1.rate / suomc_1.rate)) / ((upper(fi_1.date_range) - lower(fi_1.date_range)))::numeric) * ((upper((fi_1.date_range * crv_2.date_range)) - lower((fi_1.date_range * crv_2.date_range))))::numeric))
  • Group Key: contract_product_materialized_1.contract_item_id
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
48. 0.015 66.214 ↓ 0.0 0 1

Sort (cost=903.10..903.11 rows=1 width=104) (actual time=66.214..66.214 rows=0 loops=1)

  • Output: contract_product_materialized_1.contract_item_id, fi_1.quantity, tuomc_1.rate, suomc_1.rate, fi_1.date_range, crv_2.date_range
  • Sort Key: contract_product_materialized_1.contract_item_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
49. 0.001 66.199 ↓ 0.0 0 1

Nested Loop (cost=895.71..903.09 rows=1 width=104) (actual time=66.199..66.199 rows=0 loops=1)

  • Output: contract_product_materialized_1.contract_item_id, fi_1.quantity, tuomc_1.rate, suomc_1.rate, fi_1.date_range, crv_2.date_range
  • Inner Unique: true
  • Join Filter: ((fi_1.product_id = tuomc_1.product_id) AND (contract_product_materialized_1.uom_type_id = tuomc_1.uom_type_id))
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
50. 0.000 66.198 ↓ 0.0 0 1

Nested Loop (cost=895.43..902.69 rows=1 width=179) (actual time=66.198..66.198 rows=0 loops=1)

  • Output: crv_2.date_range, contract_product_materialized_1.contract_item_id, contract_product_materialized_1.product_id, contract_product_materialized_1.uom_type_id, fi_1.quantity, fi_1.date_range, fi_1.product_id, pro_1.product_id, suomc_1.rate, suomc_1.product_id
  • Inner Unique: true
  • Join Filter: (fi_1.product_id = contract_product_materialized_1.product_id)
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
51. 0.001 66.198 ↓ 0.0 0 1

Nested Loop (cost=894.87..899.20 rows=1 width=147) (actual time=66.198..66.198 rows=0 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, fi_1.quantity, fi_1.date_range, fi_1.product_id, pro_1.product_id, suomc_1.rate, suomc_1.product_id
  • Inner Unique: true
  • Join Filter: ((fi_1.product_id = suomc_1.product_id) AND (fi_1.uom_type_id = suomc_1.uom_type_id))
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
52. 0.001 66.197 ↓ 0.0 0 1

Nested Loop (cost=894.59..898.29 rows=2 width=142) (actual time=66.197..66.197 rows=0 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, fi_1.quantity, fi_1.date_range, fi_1.product_id, fi_1.uom_type_id, pro_1.product_id
  • Inner Unique: true
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
53. 0.003 66.196 ↓ 0.0 0 1

Merge Join (cost=894.31..897.63 rows=2 width=126) (actual time=66.196..66.196 rows=0 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, fi_1.quantity, fi_1.date_range, fi_1.product_id, fi_1.uom_type_id
  • Merge Cond: (cmp_1.company_id = fi_1.company_id)
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
54. 0.001 65.653 ↑ 164.0 1 1

Unique (cost=887.40..888.63 rows=164 width=36) (actual time=65.653..65.653 rows=1 loops=1)

  • Output: cav_3.contract_amend_version_id, cmp_1.company_id, ct_1.internal_company_type
  • Buffers: shared hit=70,021
55. 4.224 65.652 ↑ 164.0 1 1

Sort (cost=887.40..887.81 rows=164 width=36) (actual time=65.652..65.652 rows=1 loops=1)

  • Output: cav_3.contract_amend_version_id, cmp_1.company_id, ct_1.internal_company_type
  • Sort Key: cmp_1.company_id, ct_1.internal_company_type
  • Sort Method: quicksort Memory: 1,190kB
  • Buffers: shared hit=70,021
56. 2.724 61.428 ↓ 62.8 10,305 1

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

  • Output: cav_3.contract_amend_version_id, cmp_1.company_id, ct_1.internal_company_type
  • Inner Unique: true
  • Hash Cond: (cmp_1.company_type_id = ct_1.company_type_id)
  • Buffers: shared hit=70,021
57. 8.726 58.692 ↓ 62.8 10,305 1

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

  • Output: cav_3.contract_amend_version_id, cmp_1.company_id, cmp_1.company_type_id
  • Inner Unique: true
  • Buffers: shared hit=70,020
58. 8.758 39.661 ↓ 58.6 10,305 1

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

  • Output: cav_3.contract_amend_version_id, cagg_1.agg_company_id
  • Buffers: shared hit=39,066
59. 1.333 20.679 ↓ 58.4 10,224 1

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

  • Output: c_1.contractee_company_id, cav_3.contract_amend_version_id, cg_3.company_id
  • Buffers: shared hit=7,971
60. 0.039 0.479 ↓ 19.0 19 1

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

  • Output: c_1.contractee_company_id, cav_3.contract_amend_version_id, groupings_list_1.grouping_id
  • Hash Cond: (groupings_list_1.original_grouping_id = c_1.contractee_grouping_id)
  • Buffers: shared hit=85
61. 0.383 0.383 ↑ 6.0 77 1

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

  • Output: groupings_list_1.original_grouping_id, NULL::uuid, groupings_list_1.grouping_id, NULL::integer
  • Buffers: shared hit=76
62.          

CTE groupings_list

63. 0.055 0.325 ↑ 6.0 77 1

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

  • Buffers: shared hit=76
64. 0.039 0.039 ↑ 1.0 34 1

Seq Scan on client_kronos.grouping_company pg_1 (cost=0.00..19.34 rows=34 width=52) (actual time=0.005..0.039 rows=34 loops=1)

  • Output: pg_1.grouping_id, pg_1.parent_grouping_id, pg_1.grouping_id, 0
  • Buffers: shared hit=19
65. 0.069 0.231 ↑ 3.1 14 3

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

  • Output: gl_1.original_grouping_id, cg_2.parent_grouping_id, cg_2.grouping_id, (gl_1.level + 1)
  • Hash Cond: (cg_2.parent_grouping_id = gl_1.grouping_id)
  • Buffers: shared hit=57
66. 0.099 0.099 ↑ 1.0 29 3

Seq Scan on client_kronos.grouping_company cg_2 (cost=0.00..19.34 rows=29 width=32) (actual time=0.004..0.033 rows=29 loops=3)

  • Output: cg_2.grouping_id, cg_2.client_id, cg_2.grouping_description, cg_2.grouping_cd, cg_2.grouping_type, cg_2.parent_grouping_id, cg_2.active_ind, cg_2.company_type_id, cg_2.grouping_company_type
  • Filter: cg_2.active_ind
  • Rows Removed by Filter: 5
  • Buffers: shared hit=57
67. 0.033 0.063 ↑ 13.1 26 3

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

  • Output: gl_1.original_grouping_id, gl_1.level, gl_1.grouping_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
68. 0.030 0.030 ↑ 13.1 26 3

WorkTable Scan on groupings_list gl_1 (cost=0.00..6.80 rows=340 width=36) (actual time=0.007..0.010 rows=26 loops=3)

  • Output: gl_1.original_grouping_id, gl_1.level, gl_1.grouping_id
69. 0.005 0.057 ↑ 1.0 1 1

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

  • Output: c_1.contractee_grouping_id, c_1.contractee_company_id, cav_3.contract_amend_version_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=9
70. 0.001 0.052 ↑ 1.0 1 1

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

  • Output: c_1.contractee_grouping_id, c_1.contractee_company_id, cav_3.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=9
71. 0.002 0.039 ↑ 1.0 1 1

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

  • Output: crv_3.contract_family_id, cav_3.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=6
72. 0.017 0.017 ↑ 1.0 1 1

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

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

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

  • Output: crv_3.contract_renew_version_id, crv_3.contract_family_id, crv_3.date_range, crv_3.create_timestamp, crv_3.dead, crv_3.renew_version
  • Index Cond: (crv_3.contract_renew_version_id = cav_3.contract_renew_version_id)
  • Buffers: shared hit=3
74. 0.012 0.012 ↑ 1.0 1 1

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

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

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

  • Output: cg_3.company_id, cg_3.grouping_id
  • Index Cond: (cg_3.grouping_id = groupings_list_1.grouping_id)
  • Heap Fetches: 0
  • Buffers: shared hit=7,886
76. 10.224 10.224 ↑ 1.0 1 10,224

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

  • Output: cagg_1.company_id, cagg_1.agg_company_id
  • Index Cond: (cagg_1.company_id = COALESCE(c_1.contractee_company_id, cg_3.company_id))
  • Heap Fetches: 0
  • Buffers: shared hit=31,095
77. 10.305 10.305 ↑ 1.0 1 10,305

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

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

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

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

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

  • Output: ct_1.internal_company_type, ct_1.company_type_id
  • Buffers: shared hit=1
80. 0.012 0.540 ↓ 0.0 0 1

Sort (cost=6.91..6.92 rows=3 width=142) (actual time=0.540..0.540 rows=0 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, fi_1.quantity, fi_1.date_range, fi_1.company_id, fi_1.product_id, fi_1.uom_type_id
  • Sort Key: fi_1.company_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6 read=1
  • I/O Timings: read=0.467
81. 0.003 0.528 ↓ 0.0 0 1

Nested Loop (cost=0.71..6.88 rows=3 width=142) (actual time=0.527..0.528 rows=0 loops=1)

  • Output: cav_2.contract_amend_version_id, crv_2.date_range, fi_1.quantity, fi_1.date_range, fi_1.company_id, fi_1.product_id, fi_1.uom_type_id
  • Buffers: shared hit=6 read=1
  • I/O Timings: read=0.467
82. 0.003 0.033 ↑ 1.0 1 1

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

  • Output: cav_2.contract_amend_version_id, crv_2.date_range
  • Inner Unique: true
  • Buffers: shared hit=6
83. 0.022 0.022 ↑ 1.0 1 1

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

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

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

  • Output: crv_2.contract_renew_version_id, crv_2.contract_family_id, crv_2.date_range, crv_2.create_timestamp, crv_2.dead, crv_2.renew_version
  • Index Cond: (crv_2.contract_renew_version_id = cav_2.contract_renew_version_id)
  • Buffers: shared hit=3
85. 0.492 0.492 ↓ 0.0 0 1

Index Scan using forecast_base_exclusion on client_kronos.forecast_base fi_1 (cost=0.14..0.23 rows=5 width=112) (actual time=0.492..0.492 rows=0 loops=1)

  • Output: fi_1.forecast_base_id, fi_1.product_id, fi_1.company_id, fi_1.date_range, fi_1.uom_type_id, fi_1.quantity
  • Index Cond: (fi_1.date_range && crv_2.date_range)
  • Buffers: shared read=1
  • I/O Timings: read=0.467
86. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_pkey on client_kronos.product pro_1 (cost=0.28..0.33 rows=1 width=16) (never executed)

  • Output: pro_1.product_id
  • Index Cond: (pro_1.product_id = fi_1.product_id)
  • Heap Fetches: 0
87. 0.000 0.000 ↓ 0.0 0

Index Scan using product_uom_conversion_pkey on client_kronos.product_uom_conversion suomc_1 (cost=0.28..0.41 rows=3 width=37) (never executed)

  • Output: suomc_1.product_id, suomc_1.uom_type_id, suomc_1.rate
  • Index Cond: (suomc_1.product_id = pro_1.product_id)
88. 0.000 0.000 ↓ 0.0 0

Index Scan using contract_product_materialized_pkey on client_kronos.contract_product_materialized contract_product_materialized_1 (cost=0.56..3.47 rows=1 width=64) (never executed)

  • Output: contract_product_materialized_1.contract_item_id, contract_product_materialized_1.contract_id, contract_product_materialized_1.product_id, contract_product_materialized_1.estimated_quantity, contract_product_materialized_1.uom_type_id, contract_product_materialized_1.rebate_deviated_value, contract_product_materialized_1.rebate_deviated_type, contract_product_materialized_1.inactive_override, contract_product_materialized_1.rebate_direct_value, contract_product_materialized_1.rebate_direct_type, contract_product_materialized_1.rebate_direct_decimal_model, contract_product_materialized_1.rebate_deviated_decimal_model, contract_product_materialized_1.won, contract_product_materialized_1.rebate_growth_type, contract_product_materialized_1.rebate_growth_decimal_model, contract_product_materialized_1.lift_quantity
  • Index Cond: ((contract_product_materialized_1.contract_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid) AND (contract_product_materialized_1.product_id = suomc_1.product_id))
89. 0.000 0.000 ↓ 0.0 0

Index Scan using product_uom_conversion_pkey on client_kronos.product_uom_conversion tuomc_1 (cost=0.28..0.36 rows=3 width=37) (never executed)

  • Output: tuomc_1.product_id, tuomc_1.uom_type_id, tuomc_1.rate
  • Index Cond: (tuomc_1.product_id = suomc_1.product_id)
90. 0.004 66.594 ↑ 4.0 1 1

Nested Loop Left Join (cost=1.94..7.96 rows=4 width=48) (actual time=66.592..66.594 rows=1 loops=1)

  • Output: ci.contract_item_id, base.quantity
  • Join Filter: (base.contract_item_id = ci.contract_item_id)
  • Buffers: shared hit=70,029 read=3
  • I/O Timings: read=0.775
91. 0.112 0.373 ↑ 4.0 1 1

Bitmap Heap Scan on client_kronos.contract_item ci (cost=1.94..7.86 rows=4 width=16) (actual time=0.372..0.373 rows=1 loops=1)

  • Output: ci.contract_item_id, ci.contract_id, ci.product_id, ci.grouping_id, ci.estimated_quantity, ci.uom_type_id, ci.rebate_deviated_value, ci.rebate_deviated_type, ci.inactive_override, ci.rebate_direct_value, ci.rebate_direct_type, ci.rebate_direct_decimal_model, ci.rebate_deviated_decimal_model, ci.won, ci.rebate_growth_type, ci.rebate_growth_decimal_model, ci.lift_quantity
  • Recheck Cond: (ci.contract_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid)
  • Heap Blocks: exact=1
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=0.308
92. 0.261 0.261 ↑ 4.0 1 1

Bitmap Index Scan on contract_item_product_unique (cost=0.00..1.94 rows=4 width=0) (actual time=0.261..0.261 rows=1 loops=1)

  • Index Cond: (ci.contract_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid)
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=0.207
93. 66.217 66.217 ↓ 0.0 0 1

CTE Scan on base (cost=0.00..0.02 rows=1 width=48) (actual time=66.217..66.217 rows=0 loops=1)

  • Output: base.contract_item_id, base.quantity
  • Buffers: shared hit=70,027 read=1
  • I/O Timings: read=0.467
94. 63.688 63.688 ↓ 0.0 0 1

CTE Scan on obj (cost=0.00..0.02 rows=1 width=48) (actual time=63.688..63.688 rows=0 loops=1)

  • Output: obj.contract_item_id, obj.quantity
  • Buffers: shared hit=70,028
Planning time : 37.498 ms
Execution time : 131.040 ms