explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YP2G

Settings
# exclusive inclusive rows x rows loops node
1. 16.381 8,544.971 ↓ 0.0 0 1

Hash Join (cost=109,364.14..403,583.26 rows=77,498 width=205) (actual time=8,544.971..8,544.971 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
  • Hash Cond: (crv.contract_family_id = xcf.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: 11768
  • Buffers: shared hit=24278, local hit=2 read=37913, temp read=76674 written=76612
  • I/O Timings: read=146.641
2. 1,184.503 8,526.511 ↑ 1.3 53,834 1

Hash Anti Join (cost=109,149.26..401,881.71 rows=72,138 width=157) (actual time=1,141.376..8,526.511 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=24159, local hit=2 read=37906, temp read=76674 written=76612
  • I/O Timings: read=146.611
3. 1,939.111 6,751.185 ↓ 8.7 1,043,728 1

Hash Join (cost=52,383.49..325,869.51 rows=119,824 width=157) (actual time=436.176..6,751.185 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
  • Hash Cond: ((cav.contract_amend_version_id = contract_product_materialized.contract_id) AND (s.product_id = contract_product_materialized.product_id))
  • Buffers: shared hit=24159, local hit=2 read=13925, temp read=48980 written=48948
  • I/O Timings: read=58.949
4. 4,164.481 4,428.469 ↓ 2.3 1,939,149 1

Hash Join (cost=2,956.24..226,427.06 rows=853,182 width=153) (actual time=51.417..4,428.469 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
  • Hash Cond: (s.company_id = cagg.company_id)
  • Join Filter: ((s.sale_reporting_date >= lower(crv.date_range)) AND (s.sale_reporting_date <= (upper(crv.date_range) - 1)))
  • Rows Removed by Join Filter: 13635225
  • Buffers: shared hit=391, local hit=2 read=13925, temp read=4745 written=4743
  • I/O Timings: read=58.949
5. 212.732 212.732 ↓ 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.010..212.732 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=57.676
6. 23.008 51.256 ↓ 1.6 71,246 1

Hash (cost=2,409.64..2,409.64 rows=43,728 width=78) (actual time=51.256..51.256 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
  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 7169kB
  • Buffers: shared hit=391, local hit=2 read=347, temp written=405
  • I/O Timings: read=1.274
7. 17.620 28.248 ↓ 1.6 71,246 1

Hash Join (cost=786.88..2,409.64 rows=43,728 width=78) (actual time=5.753..28.248 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: (cagg.contract_family_id = crv.contract_family_id)
  • Buffers: shared hit=391, local hit=2 read=347
  • I/O Timings: read=1.274
8. 4.895 4.895 ↑ 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.895 rows=45,412 loops=1)

  • Output: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=332
  • I/O Timings: read=1.221
9. 0.534 5.733 ↑ 1.0 2,656 1

Hash (cost=753.36..753.36 rows=2,682 width=46) (actual time=5.733..5.733 rows=2,656 loops=1)

  • Output: crv.contract_family_id, crv.date_range, cav.contract_amend_version_id
  • Buckets: 4096 Batches: 1 Memory Usage: 235kB
  • Buffers: shared hit=391, local read=15
  • I/O Timings: read=0.052
10. 1.224 5.199 ↑ 1.0 2,656 1

Hash Join (cost=494.14..753.36 rows=2,682 width=46) (actual time=3.507..5.199 rows=2,656 loops=1)

  • Output: crv.contract_family_id, crv.date_range, cav.contract_amend_version_id
  • Hash Cond: (crv.contract_renew_version_id = crx.contract_renew_version_id)
  • Buffers: shared hit=391, local read=15
  • I/O Timings: read=0.052
11. 0.479 0.479 ↓ 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.479 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
12. 0.504 3.496 ↓ 1.7 2,656 1

Hash (cost=474.40..474.40 rows=1,579 width=48) (actual time=3.496..3.496 rows=2,656 loops=1)

  • Output: crx.contract_renew_version_id, cav.contract_renew_version_id, cav.contract_amend_version_id
  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 240kB
  • Buffers: shared hit=227, local read=15
  • I/O Timings: read=0.052
13. 0.940 2.992 ↓ 1.7 2,656 1

Hash Join (cost=74.76..474.40 rows=1,579 width=48) (actual time=0.636..2.992 rows=2,656 loops=1)

  • Output: crx.contract_renew_version_id, cav.contract_renew_version_id, cav.contract_amend_version_id
  • Hash Cond: (cav.contract_renew_version_id = crx.contract_renew_version_id)
  • Buffers: shared hit=227, local read=15
  • I/O Timings: read=0.052
14. 1.431 1.431 ↑ 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.007..1.431 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
15. 0.335 0.621 ↑ 1.0 2,656 1

Hash (cost=41.56..41.56 rows=2,656 width=16) (actual time=0.621..0.621 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.052
16. 0.286 0.286 ↑ 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.286 rows=2,656 loops=1)

  • Output: crx.contract_renew_version_id
  • Buffers: local read=15
  • I/O Timings: read=0.052
17. 225.517 383.605 ↓ 1.0 738,085 1

Hash (cost=31,148.50..31,148.50 rows=738,050 width=52) (actual time=383.605..383.605 rows=738,085 loops=1)

  • Output: contract_product_materialized.contract_item_id, contract_product_materialized.contract_id, contract_product_materialized.product_id, contract_product_materialized.rebate_direct_value
  • Buckets: 131072 Batches: 16 Memory Usage: 4860kB
  • Buffers: shared hit=23768, temp written=6154
18. 158.088 158.088 ↓ 1.0 738,085 1

Seq Scan on client_lactalis.contract_product_materialized (cost=0.00..31,148.50 rows=738,050 width=52) (actual time=0.008..158.088 rows=738,085 loops=1)

  • Output: contract_product_materialized.contract_item_id, contract_product_materialized.contract_id, contract_product_materialized.product_id, contract_product_materialized.rebate_direct_value
  • Buffers: shared hit=23768
19. 275.264 590.823 ↑ 1.0 1,029,791 1

Hash (cost=34,278.91..34,278.91 rows=1,029,791 width=32) (actual time=590.823..590.823 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=87.662
20. 315.559 315.559 ↑ 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..315.559 rows=1,029,791 loops=1)

  • Output: x.sale_id, x.contract_item_id
  • Buffers: local read=23981
  • I/O Timings: read=87.662
21. 0.239 2.079 ↓ 1.0 1,257 1

Hash (cost=199.90..199.90 rows=1,198 width=56) (actual time=2.079..2.079 rows=1,257 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, xcf.contract_family_id, gcp.parent_company_id
  • Buckets: 2048 Batches: 1 Memory Usage: 105kB
  • Buffers: shared hit=103, local read=7
  • I/O Timings: read=0.029
22. 0.223 1.840 ↓ 1.0 1,257 1

Hash Left Join (cost=157.44..199.90 rows=1,198 width=56) (actual time=0.968..1.840 rows=1,257 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contract_type, xcf.contract_family_id, gcp.parent_company_id
  • Inner Unique: true
  • Hash Cond: (cf.contractee_grouping_id = gcp.grouping_id)
  • Buffers: shared hit=103, local read=7
  • I/O Timings: read=0.029
23. 0.179 1.600 ↓ 1.0 1,257 1

Hash Anti Join (cost=156.21..195.51 rows=1,198 width=56) (actual time=0.947..1.600 rows=1,257 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contractee_grouping_id, cf.contract_type, xcf.contract_family_id
  • Hash Cond: (cf.contractee_grouping_id = gc.grouping_id)
  • Buffers: shared hit=102, local read=7
  • I/O Timings: read=0.029
24. 0.344 1.357 ↑ 1.0 1,284 1

Hash Join (cost=102.42..125.65 rows=1,284 width=56) (actual time=0.878..1.357 rows=1,284 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contractee_grouping_id, cf.contract_type, xcf.contract_family_id
  • Inner Unique: true
  • Hash Cond: (xcf.contract_family_id = cf.contract_family_id)
  • Buffers: shared hit=50, local read=7
  • I/O Timings: read=0.029
25. 0.164 0.164 ↑ 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.019..0.164 rows=1,284 loops=1)

  • Output: xcf.contract_family_id
  • Buffers: local read=7
  • I/O Timings: read=0.029
26. 0.389 0.849 ↓ 1.0 2,411 1

Hash (cost=73.30..73.30 rows=2,330 width=40) (actual time=0.849..0.849 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=50
27. 0.460 0.460 ↓ 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.460 rows=2,411 loops=1)

  • Output: cf.cost_basis_type, cf.contract_family_id, cf.contractee_grouping_id, cf.contract_type
  • Buffers: shared hit=50
28. 0.002 0.064 ↑ 1.0 9 1

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

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

Seq Scan on client_lactalis.grouping_company gc (cost=0.00..53.67 rows=9 width=16) (actual time=0.006..0.062 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
30. 0.003 0.017 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=32) (actual time=0.017..0.017 rows=10 loops=1)

  • Output: gcp.parent_company_id, gcp.grouping_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
31. 0.014 0.014 ↑ 1.0 10 1

Seq Scan on client_lactalis.grouping_company_parent gcp (cost=0.00..1.10 rows=10 width=32) (actual time=0.003..0.014 rows=10 loops=1)

  • Output: gcp.parent_company_id, gcp.grouping_id
  • Buffers: shared hit=1
Planning time : 57.642 ms
Execution time : 8,545.314 ms