explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fXJk

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 141,893.189 ↑ 4.0 1 1

Nested Loop Left Join (cost=32,436.97..32,443.09 rows=4 width=80) (actual time=141,893.182..141,893.189 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=70,319 read=2, temp read=1,049,588 written=1,562,252
  • I/O Timings: read=0.315
2.          

CTE param

3. 37,490.673 41,710.968 ↓ 139.6 22,507,830 1

Sort (cost=22,950.56..23,353.58 rows=161,205 width=78) (actual time=37,020.622..41,710.968 rows=22,507,830 loops=1)

  • Output: contract_product_materialized.product_id, cmp.company_id, contract_product_materialized.contract_item_id, crv.date_range, contract_product_materialized.uom_type_id
  • Sort Key: contract_product_materialized.product_id, cmp.company_id
  • Sort Method: external merge Disk: 1,938,168kB
  • Buffers: shared hit=70,183, temp read=807,801 written=808,424
4. 2,979.683 4,220.295 ↓ 139.6 22,507,830 1

Nested Loop (cost=935.94..4,401.76 rows=161,205 width=78) (actual time=70.077..4,220.295 rows=22,507,830 loops=1)

  • Output: contract_product_materialized.product_id, cmp.company_id, contract_product_materialized.contract_item_id, crv.date_range, contract_product_materialized.uom_type_id
  • Buffers: shared hit=70,183
5. 3.499 3.760 ↓ 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=0.271..3.760 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=151
6. 0.261 0.261 ↓ 2.5 2,397 1

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

  • Index Cond: (contract_product_materialized.contract_id = '68a454a3-15e7-11ea-be40-e981416a043d'::uuid)
  • Buffers: shared hit=104
7. 1,163.454 1,236.852 ↓ 56.9 9,390 2,397

Materialize (cost=888.81..900.21 rows=165 width=46) (actual time=0.029..0.516 rows=9,390 loops=2,397)

  • Output: cav.contract_amend_version_id, crv.date_range, cmp.company_id
  • Buffers: shared hit=70,032
8. 1.114 73.398 ↓ 56.9 9,390 1

Nested Loop (cost=888.81..899.38 rows=165 width=46) (actual time=69.800..73.398 rows=9,390 loops=1)

  • Output: cav.contract_amend_version_id, crv.date_range, cmp.company_id
  • Buffers: shared hit=70,032
9. 0.004 0.029 ↑ 1.0 1 1

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

  • Output: cav.contract_amend_version_id, crv.date_range
  • Inner Unique: true
  • Buffers: shared hit=6
10. 0.015 0.015 ↑ 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.015 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
11. 0.010 0.010 ↑ 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.010 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
12. 1.867 72.255 ↓ 56.9 9,390 1

Unique (cost=888.24..889.48 rows=165 width=36) (actual time=69.773..72.255 rows=9,390 loops=1)

  • Output: cav_1.contract_amend_version_id, cmp.company_id, ct.internal_company_type
  • Buffers: shared hit=70,026
13. 4.343 70.388 ↓ 62.5 10,305 1

Sort (cost=888.24..888.66 rows=165 width=36) (actual time=69.772..70.388 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=70,026
14. 3.059 66.045 ↓ 62.5 10,305 1

Hash Join (cost=360.03..882.17 rows=165 width=36) (actual time=0.153..66.045 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
15. 9.170 62.974 ↓ 62.5 10,305 1

Nested Loop (cost=358.98..879.78 rows=165 width=48) (actual time=0.133..62.974 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
16. 0.000 43.499 ↓ 58.2 10,305 1

Nested Loop (cost=358.70..819.15 rows=177 width=32) (actual time=0.124..43.499 rows=10,305 loops=1)

  • Output: cav_1.contract_amend_version_id, cagg.agg_company_id
  • Buffers: shared hit=39,066
17. 1.303 24.386 ↓ 58.1 10,224 1

Nested Loop Left Join (cost=358.29..663.93 rows=176 width=48) (actual time=0.104..24.386 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
18. 0.037 0.454 ↓ 19.0 19 1

Hash Right Join (cost=357.87..373.54 rows=1 width=48) (actual time=0.062..0.454 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
19. 0.395 0.395 ↑ 6.0 77 1

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

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

CTE groupings_list

21. 0.063 0.333 ↑ 6.0 77 1

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

  • Buffers: shared hit=76
22. 0.045 0.045 ↑ 1.0 34 1

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

  • Output: pg.grouping_id, pg.parent_grouping_id, pg.grouping_id, 0
  • Buffers: shared hit=19
23. 0.072 0.225 ↑ 3.1 14 3

Hash Join (cost=11.05..32.23 rows=43 width=52) (actual time=0.038..0.075 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
24. 0.120 0.120 ↑ 1.0 29 3

Seq Scan on client_kronos.grouping_company cg (cost=0.00..19.34 rows=29 width=32) (actual time=0.005..0.040 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
25. 0.015 0.033 ↑ 13.1 26 3

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

  • Output: gl.original_grouping_id, gl.level, gl.grouping_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
26. 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.001..0.006 rows=26 loops=3)

  • Output: gl.original_grouping_id, gl.level, gl.grouping_id
27. 0.002 0.022 ↑ 1.0 1 1

Hash (cost=6.91..6.91 rows=1 width=48) (actual time=0.022..0.022 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
28. 0.003 0.020 ↑ 1.0 1 1

Nested Loop (cost=0.84..6.91 rows=1 width=48) (actual time=0.019..0.020 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
29. 0.002 0.009 ↑ 1.0 1 1

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

  • Output: crv_1.contract_family_id, cav_1.contract_amend_version_id
  • Inner Unique: true
  • Buffers: shared hit=6
30. 0.004 0.004 ↑ 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.004..0.004 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
31. 0.003 0.003 ↑ 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.003 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
32. 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
33. 22.629 22.629 ↑ 2.3 538 19

Index Only Scan using company_grouping_pkey on client_kronos.company_grouping cg_1 (cost=0.41..277.84 rows=1,255 width=32) (actual time=0.188..1.191 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
34. 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.87 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_1.company_id))
  • Heap Fetches: 0
  • Buffers: shared hit=31,095
35. 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
36. 0.003 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.internal_company_type, ct.company_type_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
37. 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
38.          

CTE obj

39. 0.003 51,513.022 ↓ 0.0 0 1

GroupAggregate (cost=4,540.67..4,540.73 rows=1 width=48) (actual time=51,513.022..51,513.022 rows=0 loops=1)

  • Output: param.contract_item_id, sum((((fi.quantity * (tuomc.rate / suomc.rate)) / ((upper(fi.date_range) - lower(fi.date_range)))::numeric) * ((upper((fi.date_range * param.date_range)) - lower((fi.date_range * param.date_range))))::numeric))
  • Group Key: param.contract_item_id
  • Buffers: shared hit=67, temp read=241,786 written=256,023
40. 0.004 51,513.019 ↓ 0.0 0 1

Sort (cost=4,540.67..4,540.67 rows=1 width=122) (actual time=51,513.019..51,513.019 rows=0 loops=1)

  • Output: param.contract_item_id, fi.quantity, tuomc.rate, suomc.rate, fi.date_range, param.date_range
  • Sort Key: param.contract_item_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=67, temp read=241,786 written=256,023
41. 0.002 51,513.015 ↓ 0.0 0 1

Nested Loop (cost=4,516.20..4,540.66 rows=1 width=122) (actual time=51,513.015..51,513.015 rows=0 loops=1)

  • Output: param.contract_item_id, fi.quantity, tuomc.rate, suomc.rate, fi.date_range, param.date_range
  • Inner Unique: true
  • Join Filter: ((fi.product_id = suomc.product_id) AND (fi.uom_type_id = suomc.uom_type_id))
  • Buffers: shared hit=67, temp read=241,786 written=256,023
42. 0.002 51,513.013 ↓ 0.0 0 1

Nested Loop (cost=4,515.92..4,540.28 rows=1 width=197) (actual time=51,513.013..51,513.013 rows=0 loops=1)

  • Output: param.contract_item_id, param.date_range, param.product_id, fi.quantity, fi.date_range, fi.product_id, fi.uom_type_id, pro.product_id, tuomc.rate, tuomc.product_id
  • Inner Unique: true
  • Join Filter: (fi.product_id = pro.product_id)
  • Buffers: shared hit=67, temp read=241,786 written=256,023
43. 0.007 51,513.011 ↓ 0.0 0 1

Merge Join (cost=4,515.64..4,539.97 rows=1 width=181) (actual time=51,513.011..51,513.011 rows=0 loops=1)

  • Output: param.contract_item_id, param.date_range, param.product_id, fi.quantity, fi.date_range, fi.product_id, fi.uom_type_id, tuomc.rate, tuomc.product_id
  • Merge Cond: ((param.product_id = fi.product_id) AND (param.company_id = fi.company_id))
  • Join Filter: (fi.date_range && param.date_range)
  • Buffers: shared hit=67, temp read=241,786 written=256,023
44. 32,605.300 51,512.969 ↑ 2,666.0 1 1

Sort (cost=4,476.98..4,483.64 rows=2,666 width=101) (actual time=51,512.969..51,512.969 rows=1 loops=1)

  • Output: param.contract_item_id, param.date_range, param.company_id, param.product_id, tuomc.rate, tuomc.product_id
  • Sort Key: param.product_id, param.company_id
  • Sort Method: external sort Disk: 2,048,176kB
  • Buffers: shared hit=67, temp read=241,786 written=256,023
45. 6,966.749 18,907.669 ↓ 8,442.5 22,507,830 1

Hash Join (cost=254.85..4,325.28 rows=2,666 width=101) (actual time=2.513..18,907.669 rows=22,507,830 loops=1)

  • Output: param.contract_item_id, param.date_range, param.company_id, param.product_id, tuomc.rate, tuomc.product_id
  • Inner Unique: true
  • Hash Cond: ((param.product_id = tuomc.product_id) AND (param.uom_type_id = tuomc.uom_type_id))
  • Buffers: shared hit=67, temp read=241,785 written=1
46. 11,938.554 11,938.554 ↓ 139.6 22,507,830 1

CTE Scan on param (cost=0.00..3,224.10 rows=161,205 width=96) (actual time=0.110..11,938.554 rows=22,507,830 loops=1)

  • Output: param.product_id, param.company_id, param.contract_item_id, param.date_range, param.uom_type_id
  • Buffers: temp read=241,785 written=1
47. 1.282 2.366 ↓ 1.1 7,931 1

Hash (cost=142.14..142.14 rows=7,514 width=37) (actual time=2.366..2.366 rows=7,931 loops=1)

  • Output: tuomc.rate, tuomc.product_id, tuomc.uom_type_id
  • Buckets: 8,192 Batches: 1 Memory Usage: 600kB
  • Buffers: shared hit=67
48. 1.084 1.084 ↓ 1.1 7,931 1

Seq Scan on client_kronos.product_uom_conversion tuomc (cost=0.00..142.14 rows=7,514 width=37) (actual time=0.020..1.084 rows=7,931 loops=1)

  • Output: tuomc.rate, tuomc.product_id, tuomc.uom_type_id
  • Buffers: shared hit=67
49. 0.025 0.035 ↓ 0.0 0 1

Sort (cost=38.66..39.96 rows=520 width=112) (actual time=0.035..0.035 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.company_id, fi.product_id, fi.uom_type_id
  • Sort Key: fi.product_id, fi.company_id
  • Sort Method: quicksort Memory: 25kB
50. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on client_kronos.forecast_objective fi (cost=0.00..15.20 rows=520 width=112) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: fi.quantity, fi.date_range, fi.company_id, fi.product_id, fi.uom_type_id
51. 0.000 0.000 ↓ 0.0 0

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

  • Output: pro.product_id
  • Index Cond: (pro.product_id = param.product_id)
  • Heap Fetches: 0
52. 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.34 rows=3 width=37) (never executed)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Index Cond: (suomc.product_id = param.product_id)
53.          

CTE base

54. 0.002 90,379.815 ↓ 0.0 0 1

GroupAggregate (cost=4,540.67..4,540.73 rows=1 width=48) (actual time=90,379.815..90,379.815 rows=0 loops=1)

  • Output: param_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 * param_1.date_range)) - lower((fi_1.date_range * param_1.date_range))))::numeric))
  • Group Key: param_1.contract_item_id
  • Buffers: shared hit=70,250, temp read=807,802 written=1,306,229
55. 0.008 90,379.813 ↓ 0.0 0 1

Sort (cost=4,540.67..4,540.67 rows=1 width=122) (actual time=90,379.812..90,379.813 rows=0 loops=1)

  • Output: param_1.contract_item_id, fi_1.quantity, tuomc_1.rate, suomc_1.rate, fi_1.date_range, param_1.date_range
  • Sort Key: param_1.contract_item_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=70,250, temp read=807,802 written=1,306,229
56. 0.000 90,379.805 ↓ 0.0 0 1

Nested Loop (cost=4,516.20..4,540.66 rows=1 width=122) (actual time=90,379.805..90,379.805 rows=0 loops=1)

  • Output: param_1.contract_item_id, fi_1.quantity, tuomc_1.rate, suomc_1.rate, fi_1.date_range, param_1.date_range
  • 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,250, temp read=807,802 written=1,306,229
57. 0.002 90,379.805 ↓ 0.0 0 1

Nested Loop (cost=4,515.92..4,540.28 rows=1 width=197) (actual time=90,379.805..90,379.805 rows=0 loops=1)

  • Output: param_1.contract_item_id, param_1.date_range, param_1.product_id, fi_1.quantity, fi_1.date_range, fi_1.product_id, fi_1.uom_type_id, pro_1.product_id, tuomc_1.rate, tuomc_1.product_id
  • Inner Unique: true
  • Join Filter: (fi_1.product_id = pro_1.product_id)
  • Buffers: shared hit=70,250, temp read=807,802 written=1,306,229
58. 0.006 90,379.803 ↓ 0.0 0 1

Merge Join (cost=4,515.64..4,539.97 rows=1 width=181) (actual time=90,379.803..90,379.803 rows=0 loops=1)

  • Output: param_1.contract_item_id, param_1.date_range, param_1.product_id, fi_1.quantity, fi_1.date_range, fi_1.product_id, fi_1.uom_type_id, tuomc_1.rate, tuomc_1.product_id
  • Merge Cond: ((param_1.product_id = fi_1.product_id) AND (param_1.company_id = fi_1.company_id))
  • Join Filter: (fi_1.date_range && param_1.date_range)
  • Buffers: shared hit=70,250, temp read=807,802 written=1,306,229
59. 34,782.027 90,379.760 ↑ 2,666.0 1 1

Sort (cost=4,476.98..4,483.64 rows=2,666 width=101) (actual time=90,379.760..90,379.760 rows=1 loops=1)

  • Output: param_1.contract_item_id, param_1.date_range, param_1.company_id, param_1.product_id, tuomc_1.rate, tuomc_1.product_id
  • Sort Key: param_1.product_id, param_1.company_id
  • Sort Method: external sort Disk: 2,048,176kB
  • Buffers: shared hit=70,250, temp read=807,802 written=1,306,229
60. 7,149.563 55,597.733 ↓ 8,442.5 22,507,830 1

Hash Join (cost=254.85..4,325.28 rows=2,666 width=101) (actual time=37,024.288..55,597.733 rows=22,507,830 loops=1)

  • Output: param_1.contract_item_id, param_1.date_range, param_1.company_id, param_1.product_id, tuomc_1.rate, tuomc_1.product_id
  • Inner Unique: true
  • Hash Cond: ((param_1.product_id = tuomc_1.product_id) AND (param_1.uom_type_id = tuomc_1.uom_type_id))
  • Buffers: shared hit=70,250, temp read=807,801 written=1,050,207
61. 48,444.554 48,444.554 ↓ 139.6 22,507,830 1

CTE Scan on param param_1 (cost=0.00..3,224.10 rows=161,205 width=96) (actual time=37,020.628..48,444.554 rows=22,507,830 loops=1)

  • Output: param_1.product_id, param_1.company_id, param_1.contract_item_id, param_1.date_range, param_1.uom_type_id
  • Buffers: shared hit=70,183, temp read=807,801 written=1,050,207
62. 1.549 3.616 ↓ 1.1 7,931 1

Hash (cost=142.14..142.14 rows=7,514 width=37) (actual time=3.616..3.616 rows=7,931 loops=1)

  • Output: tuomc_1.rate, tuomc_1.product_id, tuomc_1.uom_type_id
  • Buckets: 8,192 Batches: 1 Memory Usage: 600kB
  • Buffers: shared hit=67
63. 2.067 2.067 ↓ 1.1 7,931 1

Seq Scan on client_kronos.product_uom_conversion tuomc_1 (cost=0.00..142.14 rows=7,514 width=37) (actual time=0.026..2.067 rows=7,931 loops=1)

  • Output: tuomc_1.rate, tuomc_1.product_id, tuomc_1.uom_type_id
  • Buffers: shared hit=67
64. 0.026 0.037 ↓ 0.0 0 1

Sort (cost=38.66..39.96 rows=520 width=112) (actual time=0.037..0.037 rows=0 loops=1)

  • Output: fi_1.quantity, fi_1.date_range, fi_1.company_id, fi_1.product_id, fi_1.uom_type_id
  • Sort Key: fi_1.product_id, fi_1.company_id
  • Sort Method: quicksort Memory: 25kB
65. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on client_kronos.forecast_base fi_1 (cost=0.00..15.20 rows=520 width=112) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: fi_1.quantity, fi_1.date_range, fi_1.company_id, fi_1.product_id, fi_1.uom_type_id
66. 0.000 0.000 ↓ 0.0 0

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

  • Output: pro_1.product_id
  • Index Cond: (pro_1.product_id = param_1.product_id)
  • Heap Fetches: 0
67. 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.34 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 = param_1.product_id)
68. 0.003 90,380.157 ↑ 4.0 1 1

Nested Loop Left Join (cost=1.94..7.96 rows=4 width=48) (actual time=90,380.152..90,380.157 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,252 read=2, temp read=807,802 written=1,306,229
  • I/O Timings: read=0.315
69. 0.010 0.337 ↑ 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.332..0.337 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.315
70. 0.327 0.327 ↑ 4.0 1 1

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

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

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

  • Output: base.contract_item_id, base.quantity
  • Buffers: shared hit=70,250, temp read=807,802 written=1,306,229
72. 51,513.024 51,513.024 ↓ 0.0 0 1

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

  • Output: obj.contract_item_id, obj.quantity
  • Buffers: shared hit=67, temp read=241,786 written=256,023
Planning time : 7.238 ms
Execution time : 142,366.624 ms