explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jS3n

Settings
# exclusive inclusive rows x rows loops node
1. 33,081.652 150,261.445 ↓ 732.0 10,575,286 1

Unique (cost=356,692.81..359,726.68 rows=14,447 width=2,144) (actual time=110,517.013..150,261.445 rows=10,575,286 loops=1)

  • Buffers: shared hit=9558 read=240326, temp read=1903619 written=1905117
2. 78,404.200 117,179.793 ↓ 732.0 10,575,286 1

Sort (cost=356,692.81..356,728.93 rows=14,447 width=2,144) (actual time=110,517.011..117,179.793 rows=10,575,286 loops=1)

  • Sort Key: fc.contract_sk, fc.source_id, fc.contract_detail_id, fc.client_sk, fc.product_sk, fc.lumpsum_sk, fc.account_sk, fc.uom_sk, fc.contract_family_sk, fc.contract_renew_version_sk, fc.contract_amend_version_sk, fc.start_date, fc.end_date, fc.contract_lumpsum_description, fc.event_start_date, fc.event_end_date, (CASE WHEN (fpuc.measure_rate_01 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_01) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_02 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_02) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_03 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_03) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_04 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_04) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_05 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_05) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_06 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_06) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_07 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_07) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_08 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_08) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_09 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_09) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_10 > '0'::numeric) THEN (fc.rebate_direct_rate / fpuc.measure_rate_10) ELSE '0'::numeric END), fc.rebate_direct_percent, (CASE WHEN (fpuc.measure_rate_01 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_01) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_02 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_02) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_03 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_03) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_04 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_04) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_05 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_05) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_06 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_06) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_07 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_07) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_08 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_08) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_09 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_09) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_10 > '0'::numeric) THEN (fc.rebate_direct_price / fpuc.measure_rate_10) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_01 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_01) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_02 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_02) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_03 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_03) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_04 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_04) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_05 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_05) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_06 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_06) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_07 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_07) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_08 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_08) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_09 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_09) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_10 > '0'::numeric) THEN (fc.rebate_deviated_rate / fpuc.measure_rate_10) ELSE '0'::numeric END), fc.rebate_deviated_percent, (CASE WHEN (fpuc.measure_rate_01 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_01) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_02 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_02) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_03 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_03) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_04 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_04) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_05 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_05) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_06 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_06) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_07 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_07) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_08 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_08) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_09 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_09) ELSE '0'::numeric END), (CASE WHEN (fpuc.measure_rate_10 > '0'::numeric) THEN (fc.rebate_deviated_price / fpuc.measure_rate_10) ELSE '0'::numeric END), fc.lumpsum_amount, ((fc.estimated_quantity * fpuc.measure_rate_01)), ((fc.estimated_quantity * fpuc.measure_rate_02)), ((fc.estimated_quantity * fpuc.measure_rate_03)), ((fc.estimated_quantity * fpuc.measure_rate_04)), ((fc.estimated_quantity * fpuc.measure_rate_05)), ((fc.estimated_quantity * fpuc.measure_rate_06)), ((fc.estimated_quantity * fpuc.measure_rate_07)), ((fc.estimated_quantity * fpuc.measure_rate_08)), ((fc.estimated_quantity * fpuc.measure_rate_09)), ((fc.estimated_quantity * fpuc.measure_rate_10)), fc.probable_exposure, amend.claimable_ind, fc.contract_product_item_price, (CASE WHEN (fpuc_product.measure_rate_01 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_01) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_02 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_02) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_03 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_03) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_04 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_04) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_05 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_05) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_06 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_06) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_07 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_07) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_08 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_08) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_09 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_09) ELSE '0'::numeric END), (CASE WHEN (fpuc_product.measure_rate_10 > '0'::numeric) THEN (p.default_price / fpuc_product.measure_rate_10) ELSE '0'::numeric END), (CASE WHEN ((fc.rebate_deviated_rate > '0'::numeric) AND (fc.rebate_deviated_percent > '0'::numeric)) THEN 'DIRECT/DEVIATED'::text WHEN ((fc.rebate_direct_rate > '0'::numeric) AND (fc.rebate_deviated_price > '0'::numeric)) THEN 'DIRECT/DEVIATED'::text WHEN ((fc.rebate_deviated_price > '0'::numeric) AND (fc.rebate_direct_price > '0'::numeric)) THEN 'DIRECT/DEVIATED'::text WHEN ((fc.rebate_direct_rate > '0'::numeric) AND (fc.lumpsum_amount > '0'::numeric)) THEN 'DIRECT/LUMPSUM'::text WHEN ((fc.rebate_direct_rate > '0'::numeric) OR (fc.rebate_direct_percent > '0'::numeric) OR (fc.rebate_direct_price > '0'::numeric)) THEN 'DIRECT'::text WHEN ((fc.rebate_deviated_rate > '0'::numeric) OR (fc.rebate_deviated_percent > '0'::numeric) OR (fc.rebate_deviated_price > '0'::numeric)) THEN 'DEVIATED'::text WHEN (fc.lumpsum_amount > '0'::numeric) THEN 'LUMPSUM'::text ELSE 'Unknown'::text END)
  • Sort Method: external merge Disk: 3161640kB
  • Buffers: shared hit=9558 read=240326, temp read=1903619 written=1905117
3. 0.000 38,775.593 ↓ 732.0 10,575,286 1

Gather (cost=21,036.71..342,310.63 rows=14,447 width=2,144) (actual time=8,324.922..38,775.593 rows=10,575,286 loops=1)

  • Buffers: shared hit=9538 read=240326, temp read=224162 written=224228
4. 24,981.011 40,475.723 ↓ 585.6 3,525,095 3

Hash Join (cost=20,036.71..339,865.93 rows=6,020 width=2,144) (actual time=8,269.559..40,475.723 rows=3,525,095 loops=3)

  • Buffers: shared hit=9538 read=240326, temp read=224162 written=224228
5. 12,189.217 15,443.734 ↓ 585.6 3,525,095 3

Hash Join (cost=15,468.49..333,368.8 rows=6,020 width=439) (actual time=8,217.163..15,443.734 rows=3,525,095 loops=3)

  • Buffers: shared hit=6330 read=240326, temp read=224162 written=224228
6. 2,767.216 2,767.216 ↑ 1.3 3,525,471 3

Seq Scan on fact_contract fc (cost=0..284,812.23 rows=4,407,023 width=219) (actual time=0.021..2,767.216 rows=3,525,471 loops=3)

  • Buffers: shared hit=416 read=240326
7. 125.610 487.301 ↓ 15.7 54,443 3

Hash (cost=15,416.49..15,416.49 rows=3,467 width=236) (actual time=487.301..487.301 rows=54,443 loops=3)

  • Buffers: shared hit=5914, temp written=1448
8. 66.500 361.691 ↓ 15.7 54,443 3

Hash Join (cost=12,791..15,416.49 rows=3,467 width=236) (actual time=282.407..361.691 rows=54,443 loops=3)

  • Buffers: shared hit=5914
9. 12.848 12.848 ↑ 1.8 54,445 3

Seq Scan on fact_product_uom_conversion fpuc (cost=0..2,244.8 rows=96,080 width=115) (actual time=0.008..12.848 rows=54,445 loops=3)

  • Buffers: shared hit=1284
10. 27.998 282.343 ↓ 7.0 25,300 3

Hash (cost=12,745.65..12,745.65 rows=3,628 width=121) (actual time=282.343..282.343 rows=25,300 loops=3)

  • Buffers: shared hit=4630
11. 36.195 254.345 ↓ 7.0 25,300 3

Hash Join (cost=5,257.34..12,745.65 rows=3,628 width=121) (actual time=135.152..254.345 rows=25,300 loops=3)

  • Buffers: shared hit=4630
12. 57.466 217.784 ↑ 1.8 54,445 3

Hash Join (cost=5,252.59..11,984.02 rows=96,080 width=129) (actual time=134.717..217.784 rows=54,445 loops=3)

  • Buffers: shared hit=4586
13. 27.216 27.216 ↑ 1.8 54,445 3

Seq Scan on fact_product_uom_conversion fpuc_product (cost=0..2,244.8 rows=96,080 width=115) (actual time=0.022..27.216 rows=54,445 loops=3)

  • Buffers: shared hit=1284
14. 45.901 133.102 ↑ 1.2 56,981 3

Hash (cost=4,014.26..4,014.26 rows=71,226 width=14) (actual time=133.102..133.102 rows=56,981 loops=3)

  • Buffers: shared hit=3302
15. 87.201 87.201 ↑ 1.2 56,981 3

Seq Scan on dim_product p (cost=0..4,014.26 rows=71,226 width=14) (actual time=0.025..87.201 rows=56,981 loops=3)

  • Buffers: shared hit=3302
16. 0.052 0.366 ↑ 1.0 110 3

Hash (cost=3.1..3.1 rows=110 width=9) (actual time=0.366..0.366 rows=110 loops=3)

  • Buffers: shared hit=6
17. 0.314 0.314 ↑ 1.0 110 3

Seq Scan on dim_uom u (cost=0..3.1 rows=110 width=9) (actual time=0.281..0.314 rows=110 loops=3)

  • Buffers: shared hit=6
18. 23.782 50.978 ↑ 1.2 52,915 3

Hash (cost=3,741.43..3,741.43 rows=66,143 width=5) (actual time=50.978..50.978 rows=52,915 loops=3)

  • Buffers: shared hit=3080
19. 27.196 27.196 ↑ 1.2 52,915 3

Seq Scan on dim_contract_amend_version amend (cost=0..3,741.43 rows=66,143 width=5) (actual time=0.021..27.196 rows=52,915 loops=3)

  • Buffers: shared hit=3080