explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3WVu

Settings
# exclusive inclusive rows x rows loops node
1. 19.982 16,298.751 ↓ 3.8 25,847 1

Hash Join (cost=24,967.19..54,037.17 rows=6,757 width=205) (actual time=1,307.863..16,298.751 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: (cf.contract_family_id = xcf.contract_family_id)
  • Buffers: shared hit=7002732 read=2020, local hit=56100 read=35880, temp read=143245 written=143181
  • I/O Timings: read=3424.666
2. 33.796 16,278.450 ↓ 6.9 25,847 1

Nested Loop Anti Join (cost=24,931.30..53,916.12 rows=3,724 width=205) (actual time=1,307.534..16,278.450 rows=25,847 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, crv.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, cagg.contract_family_id, xx.contract_family_id
  • Buffers: shared hit=7002732 read=2020, local hit=56100 read=35873, temp read=143245 written=143181
  • I/O Timings: read=3424.639
3. 6,668.894 15,314.162 ↓ 4.2 25,847 1

Hash Join (cost=24,930.87..49,778.59 rows=6,186 width=205) (actual time=1,307.495..15,314.162 rows=25,847 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, crv.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, cagg.contract_family_id, xx.contract_family_id
  • Hash Cond: ((crv.contract_family_id = cf.contract_family_id) AND (contract_product_materialized.product_id = s.product_id) AND (cagg.company_id = s.company_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=7002732 read=2020, local hit=336 read=13925, temp read=143245 written=143181
  • I/O Timings: read=2607.245
4. 1,964.268 7,721.320 ↓ 74.5 8,117,324 1

Nested Loop (cost=1,533.74..22,702.72 rows=108,999 width=98) (actual time=84.876..7,721.320 rows=8,117,324 loops=1)

  • Output: crv.contract_family_id, crv.date_range, contract_product_materialized.contract_item_id, contract_product_materialized.product_id, contract_product_materialized.rebate_direct_value, cagg.contract_family_id, cagg.company_id
  • Buffers: shared hit=7002630 read=2020, local hit=2 read=347, temp read=567 written=565
  • I/O Timings: read=2554.531
5. 40.640 128.618 ↓ 49.1 71,246 1

Hash Join (cost=1,533.32..1,925.69 rows=1,450 width=78) (actual time=84.629..128.618 rows=71,246 loops=1)

  • Output: crv.contract_family_id, crv.date_range, cav.contract_amend_version_id, cagg.contract_family_id, cagg.company_id
  • Hash Cond: (cav.contract_renew_version_id = crx.contract_renew_version_id)
  • Buffers: shared hit=391, local hit=2 read=347, temp read=567 written=565
  • I/O Timings: read=1.288
6. 3.665 3.665 ↑ 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.008..3.665 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
7. 21.999 84.313 ↓ 49.6 71,246 1

Hash (cost=1,515.37..1,515.37 rows=1,436 width=94) (actual time=84.313..84.313 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 2048) Batches: 2 (originally 1) Memory Usage: 7681kB
  • Buffers: shared hit=164, local hit=2 read=347, temp written=527
  • I/O Timings: read=1.288
8. 18.154 62.314 ↓ 49.6 71,246 1

Hash Join (cost=1,192.11..1,515.37 rows=1,436 width=94) (actual time=41.675..62.314 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: (crv.contract_family_id = cagg.contract_family_id)
  • Buffers: shared hit=164, local hit=2 read=347
  • I/O Timings: read=1.288
9. 1.922 3.163 ↑ 1.0 2,656 1

Hash Join (cost=74.76..344.75 rows=2,656 width=62) (actual time=0.659..3.163 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.059
10. 0.596 0.596 ↓ 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.596 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
11. 0.343 0.645 ↑ 1.0 2,656 1

Hash (cost=41.56..41.56 rows=2,656 width=16) (actual time=0.645..0.645 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.059
12. 0.302 0.302 ↑ 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.010..0.302 rows=2,656 loops=1)

  • Output: crx.contract_renew_version_id
  • Buffers: local read=15
  • I/O Timings: read=0.059
13. 8.953 40.997 ↓ 10.0 45,412 1

Hash (cost=1,060.59..1,060.59 rows=4,541 width=32) (actual time=40.997..40.997 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=1.230
14. 26.753 32.044 ↓ 10.0 45,412 1

HashAggregate (cost=1,015.18..1,060.59 rows=4,541 width=32) (actual time=19.656..32.044 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=1.230
15. 5.291 5.291 ↑ 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.009..5.291 rows=45,412 loops=1)

  • Output: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=332
  • I/O Timings: read=1.230
16. 5,628.434 5,628.434 ↑ 1.5 114 71,246

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.016..0.079 rows=114 loops=71,246)

  • 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=7002239 read=2020
  • I/O Timings: read=2553.243
17. 502.290 923.948 ↓ 57.3 892,779 1

Hash (cost=23,124.30..23,124.30 rows=15,590 width=161) (actual time=923.948..923.948 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
  • Buckets: 65536 (originally 16384) Batches: 32 (originally 1) Memory Usage: 7681kB
  • Buffers: shared hit=102, local hit=334 read=13578, temp written=20026
  • I/O Timings: read=52.714
18. 202.838 421.658 ↓ 57.3 892,779 1

Hash Join (cost=1,287.61..23,124.30 rows=15,590 width=161) (actual time=59.139..421.658 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=102, local hit=334 read=13578
  • I/O Timings: read=52.714
19. 159.706 159.706 ↑ 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.014..159.706 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=52.714
20. 8.643 59.114 ↓ 32.0 38,322 1

Hash (cost=1,272.63..1,272.63 rows=1,198 width=56) (actual time=59.114..59.114 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=102, local hit=334
21. 8.479 50.471 ↓ 32.0 38,322 1

Hash Anti Join (cost=1,171.39..1,272.63 rows=1,198 width=56) (actual time=19.989..50.471 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=102, local hit=334
22. 11.625 41.917 ↓ 35.4 45,412 1

Hash Join (cost=1,117.61..1,202.77 rows=1,284 width=72) (actual time=19.896..41.917 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=50, local hit=334
23. 24.784 29.421 ↓ 10.0 45,412 1

HashAggregate (cost=1,015.18..1,060.59 rows=4,541 width=32) (actual time=19.007..29.421 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
24. 4.637 4.637 ↑ 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.008..4.637 rows=45,412 loops=1)

  • Output: xx.contract_family_id, xx.company_id
  • Buffers: local hit=334
25. 0.394 0.871 ↓ 1.0 2,411 1

Hash (cost=73.30..73.30 rows=2,330 width=40) (actual time=0.871..0.871 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=50
26. 0.477 0.477 ↓ 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.004..0.477 rows=2,411 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, cf.contractee_grouping_id
  • Buffers: shared hit=50
27. 0.004 0.075 ↑ 1.0 9 1

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

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

Seq Scan on client_lactalis.grouping_company gc (cost=0.00..53.67 rows=9 width=16) (actual time=0.013..0.071 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
29. 930.492 930.492 ↓ 0.0 0 25,847

Index Only Scan using idx_sales_potential_earnings_test on pg_temp_93._sales_potential_earnings x (cost=0.42..0.66 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=25,847)

  • Output: x.sale_id, x.contract_item_id
  • Index Cond: ((x.sale_id = s.sale_id) AND (x.contract_item_id = contract_product_materialized.contract_item_id))
  • Heap Fetches: 0
  • Buffers: local hit=55764 read=21948
  • I/O Timings: read=817.393
30. 0.163 0.319 ↑ 1.0 1,284 1

Hash (cost=19.84..19.84 rows=1,284 width=16) (actual time=0.319..0.319 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.028
31. 0.156 0.156 ↑ 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.018..0.156 rows=1,284 loops=1)

  • Output: xcf.contract_family_id
  • Buffers: local read=7
  • I/O Timings: read=0.028
Planning time : 120.422 ms
Execution time : 16,303.119 ms