explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Car1

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 17,420.634 ↓ 0.0 0 1

Hash Left Join (cost=59,932.19..236,154.67 rows=698 width=205) (actual time=17,420.634..17,420.634 rows=0 loops=1)

  • Output: s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, CASE WHEN (cf.cost_basis_type = 'DELIVERED'::enum.cost_basis_type) THEN ((s.price - s.reduction) + s.freight) ELSE s.price END, s.uom_type_id, COALESCE(gcp.parent_company_id, s.company_id), cf.contract_family_id, contract_product_materialized.contract_item_id, NULL::numeric, false
  • Inner Unique: true
  • Hash Cond: (cf.contractee_grouping_id = gcp.grouping_id)
  • Buffers: shared hit=6869496 read=4685, local hit=2 read=37913, temp read=32786 written=32754
  • I/O Timings: read=4642.494
2. 10.258 17,420.633 ↓ 0.0 0 1

Hash Anti Join (cost=59,930.97..236,146.36 rows=698 width=157) (actual time=17,420.632..17,420.633 rows=0 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contractee_grouping_id, contract_product_materialized.contract_item_id, s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.reduction, s.freight, s.uom_type_id
  • Hash Cond: (cf.contractee_grouping_id = gc.grouping_id)
  • Buffers: shared hit=6869496 read=4685, local hit=2 read=37913, temp read=32786 written=32754
  • I/O Timings: read=4642.494
3. 40.963 17,409.261 ↓ 45.8 34,262 1

Hash Join (cost=59,877.18..236,083.21 rows=748 width=157) (actual time=820.152..17,409.261 rows=34,262 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contractee_grouping_id, contract_product_materialized.contract_item_id, s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.reduction, s.freight, s.uom_type_id
  • Inner Unique: true
  • Hash Cond: (xcf.contract_family_id = cf.contract_family_id)
  • Join Filter: (((cf.contract_type <> ALL ('{GROWTH_STANDARD,GROWTH_AUTOPAY}'::enum.contract_type[])) AND (contract_product_materialized.rebate_direct_value > '0'::numeric)) OR (cf.contract_type = ANY ('{GROWTH_STANDARD,GROWTH_AUTOPAY}'::enum.contract_type[])))
  • Rows Removed by Join Filter: 19572
  • Buffers: shared hit=6869495 read=4634, local hit=2 read=37913, temp read=32786 written=32754
  • I/O Timings: read=4641.505
4. 28.370 17,366.685 ↓ 70.9 53,834 1

Hash Join (cost=59,774.75..235,978.79 rows=759 width=173) (actual time=758.941..17,366.685 rows=53,834 loops=1)

  • Output: xcf.contract_family_id, crv.contract_family_id, contract_product_materialized.contract_item_id, contract_product_materialized.rebate_direct_value, s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.reduction, s.freight, s.uom_type_id, cagg.contract_family_id
  • Hash Cond: (crv.contract_family_id = xcf.contract_family_id)
  • Buffers: shared hit=6869477 read=4586, local hit=2 read=37913, temp read=32786 written=32754
  • I/O Timings: read=4640.877
5. 1,823.019 17,338.019 ↓ 82.9 53,834 1

Hash Anti Join (cost=59,738.86..235,936.97 rows=649 width=157) (actual time=758.628..17,338.019 rows=53,834 loops=1)

  • Output: crv.contract_family_id, contract_product_materialized.contract_item_id, contract_product_materialized.rebate_direct_value, s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.reduction, s.freight, s.uom_type_id, cagg.contract_family_id
  • Hash Cond: ((s.sale_id = x.sale_id) AND (contract_product_materialized.contract_item_id = x.contract_item_id))
  • Buffers: shared hit=6869477 read=4586, local hit=2 read=37906, temp read=32786 written=32754
  • I/O Timings: read=4640.850
6. 607.879 14,937.182 ↓ 968.2 1,043,728 1

Nested Loop (cost=2,973.09..172,019.82 rows=1,078 width=157) (actual time=55.333..14,937.182 rows=1,043,728 loops=1)

  • Output: crv.contract_family_id, contract_product_materialized.contract_item_id, contract_product_materialized.rebate_direct_value, s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.reduction, s.freight, s.uom_type_id, cagg.contract_family_id
  • Inner Unique: true
  • Buffers: shared hit=6869477 read=4586, local hit=2 read=13925, temp read=5092 written=5090
  • I/O Timings: read=4550.842
7. 679.575 6,572.707 ↓ 252.5 1,939,149 1

Hash Join (cost=2,972.66..148,746.38 rows=7,679 width=153) (actual time=54.833..6,572.707 rows=1,939,149 loops=1)

  • Output: crv.contract_family_id, cav.contract_amend_version_id, s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.reduction, s.freight, s.uom_type_id, cagg.contract_family_id
  • Inner Unique: true
  • Hash Cond: (crx.contract_renew_version_id = cav.contract_renew_version_id)
  • Buffers: shared hit=8 read=383, local hit=2 read=13925, temp read=5092 written=5090
  • I/O Timings: read=1435.132
8. 4,248.020 5,888.589 ↓ 255.0 1,939,149 1

Hash Join (cost=2,537.77..148,291.51 rows=7,604 width=169) (actual time=50.273..5,888.589 rows=1,939,149 loops=1)

  • Output: crv.contract_family_id, crv.contract_renew_version_id, crx.contract_renew_version_id, s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.reduction, s.freight, s.uom_type_id, cagg.contract_family_id
  • Hash Cond: (s.company_id = cagg.company_id)
  • Join Filter: (crv.date_range @> s.sale_reporting_date)
  • Rows Removed by Join Filter: 13635225
  • Buffers: shared hit=4 read=160, local hit=2 read=13925, temp read=5092 written=5090
  • I/O Timings: read=1433.683
9. 1,590.428 1,590.428 ↓ 1.0 502,100 1

Seq Scan on pg_temp_93._sales_raw s (cost=0.00..20,649.40 rows=500,867 width=105) (actual time=0.016..1,590.428 rows=502,100 loops=1)

  • Output: s.sale_id, s.sale_number, s.sale_reporting_date, s.company_id, s.product_id, s.quantity, s.price, s.uom_type_id, s.redistributor_company_id, s.broker_company_id, s.company_grouping_id, s.extended_price_broker_commission, s.freight, s.reduction
  • Filter: (s.redistributor_company_id IS NULL)
  • Rows Removed by Filter: 205040
  • Buffers: local read=13578
  • I/O Timings: read=1431.253
10. 22.676 50.141 ↓ 1.6 71,246 1

Hash (cost=1,996.47..1,996.47 rows=43,304 width=94) (actual time=50.141..50.141 rows=71,246 loops=1)

  • Output: crv.contract_family_id, crv.contract_renew_version_id, crv.date_range, crx.contract_renew_version_id, cagg.contract_family_id, cagg.company_id
  • Buckets: 65536 (originally 65536) Batches: 2 (originally 1) Memory Usage: 7681kB
  • Buffers: shared hit=4 read=160, local hit=2 read=347, temp written=481
  • I/O Timings: read=2.430
11. 18.238 27.465 ↓ 1.6 71,246 1

Hash Join (cost=377.95..1,996.47 rows=43,304 width=94) (actual time=4.299..27.465 rows=71,246 loops=1)

  • Output: crv.contract_family_id, crv.contract_renew_version_id, crv.date_range, crx.contract_renew_version_id, cagg.contract_family_id, cagg.company_id
  • Hash Cond: (cagg.contract_family_id = crv.contract_family_id)
  • Buffers: shared hit=4 read=160, local hit=2 read=347
  • I/O Timings: read=2.430
12. 4.949 4.949 ↑ 1.0 45,412 1

Seq Scan on pg_temp_93._contractee_agg cagg (cost=0.00..788.12 rows=45,412 width=32) (actual time=0.010..4.949 rows=45,412 loops=1)

  • Output: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=332
  • I/O Timings: read=1.298
13. 0.659 4.278 ↑ 1.0 2,656 1

Hash (cost=344.75..344.75 rows=2,656 width=62) (actual time=4.278..4.278 rows=2,656 loops=1)

  • Output: crv.contract_family_id, crv.contract_renew_version_id, crv.date_range, crx.contract_renew_version_id
  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
  • Buffers: shared hit=4 read=160, local read=15
  • I/O Timings: read=1.132
14. 1.324 3.619 ↑ 1.0 2,656 1

Hash Join (cost=74.76..344.75 rows=2,656 width=62) (actual time=0.770..3.619 rows=2,656 loops=1)

  • Output: crv.contract_family_id, crv.contract_renew_version_id, crv.date_range, crx.contract_renew_version_id
  • Hash Cond: (crv.contract_renew_version_id = crx.contract_renew_version_id)
  • Buffers: shared hit=4 read=160, local read=15
  • I/O Timings: read=1.132
15. 1.654 1.654 ↓ 1.0 5,845 1

Seq Scan on client_lactalis.contract_renew_version crv (cost=0.00..221.77 rows=5,777 width=46) (actual time=0.116..1.654 rows=5,845 loops=1)

  • Output: crv.contract_renew_version_id, crv.contract_family_id, crv.date_range, crv.create_timestamp, crv.dead, crv.renew_version
  • Buffers: shared hit=4 read=160
  • I/O Timings: read=1.071
16. 0.342 0.641 ↑ 1.0 2,656 1

Hash (cost=41.56..41.56 rows=2,656 width=16) (actual time=0.641..0.641 rows=2,656 loops=1)

  • Output: crx.contract_renew_version_id
  • Buckets: 4096 Batches: 1 Memory Usage: 157kB
  • Buffers: local read=15
  • I/O Timings: read=0.060
17. 0.299 0.299 ↑ 1.0 2,656 1

Seq Scan on pg_temp_93._contract_renew_version crx (cost=0.00..41.56 rows=2,656 width=16) (actual time=0.013..0.299 rows=2,656 loops=1)

  • Output: crx.contract_renew_version_id
  • Buffers: local read=15
  • I/O Timings: read=0.060
18. 1.235 4.543 ↑ 1.1 5,338 1

Hash (cost=361.97..361.97 rows=5,834 width=32) (actual time=4.543..4.543 rows=5,338 loops=1)

  • Output: cav.contract_renew_version_id, cav.contract_amend_version_id
  • Buckets: 8192 Batches: 1 Memory Usage: 398kB
  • Buffers: shared hit=4 read=223
  • I/O Timings: read=1.449
19. 3.308 3.308 ↑ 1.1 5,338 1

Seq Scan on client_lactalis.contract_amend_version cav (cost=0.00..361.97 rows=5,834 width=32) (actual time=0.150..3.308 rows=5,338 loops=1)

  • Output: cav.contract_renew_version_id, cav.contract_amend_version_id
  • Filter: (cav.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED}'::enum.contract_state[]))
  • Rows Removed by Filter: 3640
  • Buffers: shared hit=4 read=223
  • I/O Timings: read=1.449
20. 7,756.596 7,756.596 ↑ 1.0 1 1,939,149

Index Scan using contract_product_materialized_pkey on client_lactalis.contract_product_materialized (cost=0.42..3.03 rows=1 width=52) (actual time=0.004..0.004 rows=1 loops=1,939,149)

  • 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
  • Index Cond: ((contract_product_materialized.contract_id = cav.contract_amend_version_id) AND (contract_product_materialized.product_id = s.product_id))
  • Buffers: shared hit=6869469 read=4203
  • I/O Timings: read=3115.711
21. 270.461 577.818 ↑ 1.0 1,029,791 1

Hash (cost=34,278.91..34,278.91 rows=1,029,791 width=32) (actual time=577.818..577.818 rows=1,029,791 loops=1)

  • Output: x.sale_id, x.contract_item_id
  • Buckets: 131072 Batches: 16 Memory Usage: 5038kB
  • Buffers: local read=23981, temp written=6121
  • I/O Timings: read=90.008
22. 307.357 307.357 ↑ 1.0 1,029,791 1

Seq Scan on pg_temp_93._sales_potential_earnings x (cost=0.00..34,278.91 rows=1,029,791 width=32) (actual time=0.016..307.357 rows=1,029,791 loops=1)

  • Output: x.sale_id, x.contract_item_id
  • Buffers: local read=23981
  • I/O Timings: read=90.008
23. 0.162 0.296 ↑ 1.0 1,284 1

Hash (cost=19.84..19.84 rows=1,284 width=16) (actual time=0.296..0.296 rows=1,284 loops=1)

  • Output: xcf.contract_family_id
  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
  • Buffers: local read=7
  • I/O Timings: read=0.026
24. 0.134 0.134 ↑ 1.0 1,284 1

Seq Scan on pg_temp_93._contract_family xcf (cost=0.00..19.84 rows=1,284 width=16) (actual time=0.014..0.134 rows=1,284 loops=1)

  • Output: xcf.contract_family_id
  • Buffers: local read=7
  • I/O Timings: read=0.026
25. 0.447 1.613 ↓ 1.0 2,411 1

Hash (cost=73.30..73.30 rows=2,330 width=40) (actual time=1.612..1.613 rows=2,411 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contractee_grouping_id, cf.contract_type
  • Buckets: 4096 Batches: 1 Memory Usage: 166kB
  • Buffers: shared hit=2 read=48
  • I/O Timings: read=0.628
26. 1.166 1.166 ↓ 1.0 2,411 1

Seq Scan on client_lactalis.contract_family cf (cost=0.00..73.30 rows=2,330 width=40) (actual time=0.189..1.166 rows=2,411 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contractee_grouping_id, cf.contract_type
  • Buffers: shared hit=2 read=48
  • I/O Timings: read=0.628
27. 0.007 1.114 ↑ 1.0 9 1

Hash (cost=53.67..53.67 rows=9 width=16) (actual time=1.114..1.114 rows=9 loops=1)

  • Output: gc.grouping_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1 read=51
  • I/O Timings: read=0.988
28. 1.107 1.107 ↑ 1.0 9 1

Seq Scan on client_lactalis.grouping_company gc (cost=0.00..53.67 rows=9 width=16) (actual time=0.188..1.107 rows=9 loops=1)

  • Output: gc.grouping_id
  • Filter: (gc.grouping_company_type = 'BUYING_GROUP'::enum.grouping_company_type)
  • Rows Removed by Filter: 118
  • Buffers: shared hit=1 read=51
  • I/O Timings: read=0.988
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.10..1.10 rows=10 width=32) (never executed)

  • Output: gcp.parent_company_id, gcp.grouping_id
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_lactalis.grouping_company_parent gcp (cost=0.00..1.10 rows=10 width=32) (never executed)

  • Output: gcp.parent_company_id, gcp.grouping_id
Planning time : 66.191 ms
Execution time : 17,420.933 ms