explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SfNg

Settings
# exclusive inclusive rows x rows loops node
1. 145.240 1,787.551 ↓ 3.8 25,847 1

Hash Anti Join (cost=81,837.98..113,384.14 rows=6,757 width=205) (actual time=1,280.017..1,787.551 rows=25,847 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, s.redistributor_company_id, cf.contract_family_id, contract_product_materialized.contract_item_id, NULL::numeric, false
  • Hash Cond: ((s.sale_id = x.sale_id) AND (contract_product_materialized.contract_item_id = x.contract_item_id))
  • Buffers: shared hit=66684 read=1321, local hit=336 read=37913, temp read=7854 written=7822
  • I/O Timings: read=324.441
2. 134.440 1,015.104 ↓ 2.3 25,847 1

Hash Join (cost=25,072.21..48,382.59 rows=11,225 width=157) (actual time=652.255..1,015.104 rows=25,847 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_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, s.redistributor_company_id
  • Hash Cond: ((xcf.contract_family_id = cf.contract_family_id) AND (contract_product_materialized.product_id = s.product_id))
  • Join Filter: ((crv.date_range @> s.sale_reporting_date) AND (((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: 111470
  • Buffers: shared hit=66684 read=1321, local hit=336 read=13932, temp read=1128 written=1126
  • I/O Timings: read=211.232
3. 30.539 270.015 ↑ 1.4 77,290 1

Nested Loop (cost=423.25..21,534.89 rows=108,723 width=82) (actual time=4.247..270.015 rows=77,290 loops=1)

  • Output: xcf.contract_family_id, crv.contract_family_id, crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.product_id, contract_product_materialized.rebate_direct_value
  • Buffers: shared hit=66630 read=1273, local read=22
  • I/O Timings: read=157.066
4. 3.533 11.060 ↓ 1.8 2,656 1

Hash Join (cost=422.82..815.18 rows=1,446 width=62) (actual time=4.226..11.060 rows=2,656 loops=1)

  • Output: xcf.contract_family_id, crv.contract_family_id, crv.date_range, cav.contract_amend_version_id
  • Hash Cond: (cav.contract_renew_version_id = crx.contract_renew_version_id)
  • Buffers: shared hit=391, local read=22
  • I/O Timings: read=0.082
5. 3.319 3.319 ↑ 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.011..3.319 rows=5,338 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
  • Filter: (cav.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED}'::enum.contract_state[]))
  • Rows Removed by Filter: 3640
  • Buffers: shared hit=227
6. 0.683 4.208 ↓ 1.9 2,656 1

Hash (cost=404.92..404.92 rows=1,432 width=78) (actual time=4.208..4.208 rows=2,656 loops=1)

  • Output: xcf.contract_family_id, crv.contract_family_id, crv.contract_renew_version_id, crv.date_range, crx.contract_renew_version_id
  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 318kB
  • Buffers: shared hit=164, local read=22
  • I/O Timings: read=0.082
7. 0.740 3.525 ↓ 1.9 2,656 1

Hash Join (cost=110.65..404.92 rows=1,432 width=78) (actual time=0.922..3.525 rows=2,656 loops=1)

  • Output: xcf.contract_family_id, crv.contract_family_id, crv.contract_renew_version_id, crv.date_range, crx.contract_renew_version_id
  • Hash Cond: (crv.contract_family_id = xcf.contract_family_id)
  • Buffers: shared hit=164, local read=22
  • I/O Timings: read=0.082
8. 1.349 2.487 ↑ 1.0 2,656 1

Hash Join (cost=74.76..344.75 rows=2,656 width=62) (actual time=0.619..2.487 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=164, local read=15
  • I/O Timings: read=0.054
9. 0.532 0.532 ↓ 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.004..0.532 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=164
10. 0.333 0.606 ↑ 1.0 2,656 1

Hash (cost=41.56..41.56 rows=2,656 width=16) (actual time=0.606..0.606 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.054
11. 0.273 0.273 ↑ 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.011..0.273 rows=2,656 loops=1)

  • Output: crx.contract_renew_version_id
  • Buffers: local read=15
  • I/O Timings: read=0.054
12. 0.161 0.298 ↑ 1.0 1,284 1

Hash (cost=19.84..19.84 rows=1,284 width=16) (actual time=0.298..0.298 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.029
13. 0.137 0.137 ↑ 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.017..0.137 rows=1,284 loops=1)

  • Output: xcf.contract_family_id
  • Buffers: local read=7
  • I/O Timings: read=0.029
14. 228.416 228.416 ↑ 5.9 29 2,656

Index Scan using contract_product_materialized_pkey on client_lactalis.contract_product_materialized (cost=0.42..12.63 rows=170 width=52) (actual time=0.014..0.086 rows=29 loops=2,656)

  • 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)
  • Buffers: shared hit=66239 read=1273
  • I/O Timings: read=156.983
15. 26.829 610.649 ↓ 3.4 47,025 1

Hash (cost=24,440.10..24,440.10 rows=13,924 width=177) (actual time=610.649..610.649 rows=47,025 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, cagg.contract_family_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, s.redistributor_company_id, xx.contract_family_id
  • Buckets: 65536 (originally 16384) Batches: 2 (originally 1) Memory Usage: 7681kB
  • Buffers: shared hit=54 read=48, local hit=336 read=13910, temp written=639
  • I/O Timings: read=54.167
16. 146.291 583.820 ↓ 3.4 47,025 1

Hash Join (cost=2,416.31..24,440.10 rows=13,924 width=177) (actual time=107.877..583.820 rows=47,025 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, cagg.contract_family_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, s.redistributor_company_id, xx.contract_family_id
  • Inner Unique: true
  • Hash Cond: ((cf.contract_family_id = cagg.contract_family_id) AND (s.company_id = cagg.company_id))
  • Buffers: shared hit=54 read=48, local hit=336 read=13910
  • I/O Timings: read=54.167
17. 181.961 391.289 ↓ 57.3 892,779 1

Hash Join (cost=1,287.61..23,124.30 rows=15,590 width=161) (actual time=61.526..391.289 rows=892,779 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, xx.contract_family_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, s.redistributor_company_id
  • Hash Cond: (s.redistributor_company_id = xx.company_id)
  • Buffers: shared hit=54 read=48, local hit=334 read=13578
  • I/O Timings: read=51.982
18. 147.861 147.861 ↑ 1.0 205,040 1

Seq Scan on pg_temp_93._sales_raw s (cost=0.00..20,649.40 rows=206,273 width=121) (actual time=0.034..147.861 rows=205,040 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 NOT NULL)
  • Rows Removed by Filter: 502100
  • Buffers: local read=13578
  • I/O Timings: read=51.278
19. 9.569 61.467 ↓ 32.0 38,322 1

Hash (cost=1,272.63..1,272.63 rows=1,198 width=56) (actual time=61.467..61.467 rows=38,322 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, xx.contract_family_id, xx.company_id
  • Buckets: 65536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 3806kB
  • Buffers: shared hit=54 read=48, local hit=334
  • I/O Timings: read=0.704
20. 8.126 51.898 ↓ 32.0 38,322 1

Hash Anti Join (cost=1,171.39..1,272.63 rows=1,198 width=56) (actual time=21.624..51.898 rows=38,322 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, xx.contract_family_id, xx.company_id
  • Hash Cond: (cf.contractee_grouping_id = gc.grouping_id)
  • Buffers: shared hit=54 read=48, local hit=334
  • I/O Timings: read=0.704
21. 11.820 43.694 ↓ 35.4 45,412 1

Hash Join (cost=1,117.61..1,202.77 rows=1,284 width=72) (actual time=21.525..43.694 rows=45,412 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, cf.contractee_grouping_id, xx.contract_family_id, xx.company_id
  • Inner Unique: true
  • Hash Cond: (xx.contract_family_id = cf.contract_family_id)
  • Buffers: shared hit=2 read=48, local hit=334
  • I/O Timings: read=0.704
22. 25.171 30.138 ↓ 10.0 45,412 1

HashAggregate (cost=1,015.18..1,060.59 rows=4,541 width=32) (actual time=19.778..30.138 rows=45,412 loops=1)

  • Output: xx.contract_family_id, xx.company_id
  • Group Key: xx.contract_family_id, xx.company_id
  • Buffers: local hit=334
23. 4.967 4.967 ↑ 1.0 45,412 1

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

  • Output: xx.contract_family_id, xx.company_id
  • Buffers: local hit=334
24. 0.439 1.736 ↓ 1.0 2,411 1

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

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, cf.contractee_grouping_id
  • Buckets: 4096 Batches: 1 Memory Usage: 166kB
  • Buffers: shared hit=2 read=48
  • I/O Timings: read=0.704
25. 1.297 1.297 ↓ 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.217..1.297 rows=2,411 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, cf.contractee_grouping_id
  • Buffers: shared hit=2 read=48
  • I/O Timings: read=0.704
26. 0.004 0.078 ↑ 1.0 9 1

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

  • Output: gc.grouping_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=52
27. 0.074 0.074 ↑ 1.0 9 1

Seq Scan on client_lactalis.grouping_company gc (cost=0.00..53.67 rows=9 width=16) (actual time=0.015..0.074 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=52
28. 10.421 46.240 ↓ 10.0 45,412 1

Hash (cost=1,060.59..1,060.59 rows=4,541 width=32) (actual time=46.240..46.240 rows=45,412 loops=1)

  • Output: cagg.contract_family_id, cagg.company_id
  • Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 3351kB
  • Buffers: local hit=2 read=332
  • I/O Timings: read=2.185
29. 29.256 35.819 ↓ 10.0 45,412 1

HashAggregate (cost=1,015.18..1,060.59 rows=4,541 width=32) (actual time=23.612..35.819 rows=45,412 loops=1)

  • Output: cagg.contract_family_id, cagg.company_id
  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=332
  • I/O Timings: read=2.185
30. 6.563 6.563 ↑ 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.304..6.563 rows=45,412 loops=1)

  • Output: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=332
  • I/O Timings: read=2.185
31. 279.218 627.207 ↑ 1.0 1,029,791 1

Hash (cost=34,278.91..34,278.91 rows=1,029,791 width=32) (actual time=627.207..627.207 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=113.209
32. 347.989 347.989 ↑ 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.036..347.989 rows=1,029,791 loops=1)

  • Output: x.sale_id, x.contract_item_id
  • Buffers: local read=23981
  • I/O Timings: read=113.209
Planning time : 94.098 ms
Execution time : 1,789.405 ms