explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DIKF : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #hsir; plan #DbO0; plan #B5ZB; plan #L1As; plan #MgvC; plan #WGY

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 12.240 24.238 ↑ 262.8 60 1

HashAggregate (cost=7,543.28..7,898.11 rows=15,770 width=224) (actual time=24.059..24.238 rows=60 loops=1)

  • Output: bsl.bustructurecode, bsl.bustructureid, bsl.level, pnl.nomenclatureid, pnl.level, sum(psd.marginfrsim), sum(CASE WHEN (purchaseprice1simarray2.simulatedsalesprice IS NOT NULL) THEN CASE WHEN (purchaseprice1simarray2.simulatedsalesprice < purchaseprice1simarray2.initialsalesprice) THEN 1 ELSE 0 END ELSE 0 END), sum(CASE WHEN (salespricesimarray2.simulatedsalesprice IS NOT NULL) THEN CASE WHEN (salespricesimarray2.simulatedsalesprice < salespricesimarray2.initialsalesprice) THEN 1 ELSE 0 END ELSE 0 END), sum(CASE WHEN (purchaseprice1simarray2.simulatedsalesprice IS NOT NULL) THEN CASE WHEN (purchaseprice1simarray2.simulatedsalesprice > purchaseprice1simarray2.initialsalesprice) THEN 1 ELSE 0 END ELSE 0 END), sum(CASE WHEN (salespricesimarray2.simulatedsalesprice IS NOT NULL) THEN CASE WHEN (salespricesimarray2.simulatedsalesprice > salespricesimarray2.initialsalesprice) THEN 1 ELSE 0 END ELSE 0 END), sum(psd.qtysim), sum(psd.revenuesim), sum(psd.revenuefrsim), sum(psd.marginsim), sum((psd.vatamountsim * (psd.qtysim)::numeric))
  • Group Key: bsl.bustructurecode, bsl.bustructureid, bsl.level, pnl.nomenclatureid, pnl.level
  • Buffers: shared hit=8,750
2. 0.652 11.998 ↑ 1.0 15,048 1

Hash Join (cost=1,019.83..6,715.36 rows=15,770 width=185) (actual time=0.904..11.998 rows=15,048 loops=1)

  • Output: bsl.bustructurecode, bsl.bustructureid, bsl.level, pnl.nomenclatureid, pnl.level, psd.marginfrsim, purchaseprice1simarray2.simulatedsalesprice, purchaseprice1simarray2.initialsalesprice, salespricesimarray2.simulatedsalesprice, salespricesimarray2.initialsalesprice, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim
  • Hash Cond: (psd.bustructureid = bsl.buid)
  • Buffers: shared hit=8,750
3. 0.000 11.176 ↓ 1.0 5,016 1

Gather (cost=1,000.72..6,472.11 rows=4,832 width=177) (actual time=0.718..11.176 rows=5,016 loops=1)

  • Output: psd.marginfrsim, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim, psd.bustructureid, salespricesimarray2.simulatedsalesprice, salespricesimarray2.initialsalesprice, purchaseprice1simarray2.simulatedsalesprice, purchaseprice1simarray2.initialsalesprice, pnl.nomenclatureid, pnl.level
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=8,745
4. 0.454 11.962 ↑ 1.1 2,508 2 / 2

Nested Loop (cost=0.72..4,988.91 rows=2,842 width=177) (actual time=0.079..11.962 rows=2,508 loops=2)

  • Output: psd.marginfrsim, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim, psd.bustructureid, salespricesimarray2.simulatedsalesprice, salespricesimarray2.initialsalesprice, purchaseprice1simarray2.simulatedsalesprice, purchaseprice1simarray2.initialsalesprice, pnl.nomenclatureid, pnl.level
  • Buffers: shared hit=8,745
  • Worker 0: actual time=0.122..17.122 rows=2,256 loops=1
  • Buffers: shared hit=3,933
5. 0.334 3.148 ↑ 1.2 418 2 / 2

Nested Loop (cost=0.28..1,445.15 rows=492 width=169) (actual time=0.036..3.148 rows=418 loops=2)

  • Output: psd.marginfrsim, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim, psd.productid, psd.bustructureid, salespricesimarray2.simulatedsalesprice, salespricesimarray2.initialsalesprice, purchaseprice1simarray2.simulatedsalesprice, purchaseprice1simarray2.initialsalesprice
  • Buffers: shared hit=1,185
  • Worker 0: actual time=0.050..3.974 rows=376 loops=1
  • Buffers: shared hit=528
6. 0.592 2.814 ↑ 1.2 418 2 / 2

Nested Loop (cost=0.28..1,435.30 rows=492 width=137) (actual time=0.032..2.814 rows=418 loops=2)

  • Output: psd.marginfrsim, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim, psd.purchaseprice1simarray, psd.productid, psd.bustructureid, salespricesimarray2.simulatedsalesprice, salespricesimarray2.initialsalesprice
  • Buffers: shared hit=1,185
  • Worker 0: actual time=0.046..3.674 rows=376 loops=1
  • Buffers: shared hit=528
7. 1.804 1.804 ↑ 1.2 418 2 / 2

Parallel Index Scan using price_strategy_detail_2094_pk on pricing.price_strategy_detail_2094 psd (cost=0.28..1,425.46 rows=492 width=482) (actual time=0.020..1.804 rows=418 loops=2)

  • Output: psd.productid, psd.bustructureid, psd.nomenclatureid, psd.bulevel, psd.nomenclatureparentid, psd.startpromodate, psd.endpromodate, psd.qtysimarray, psd.qtysim, psd.qty, psd.vatrate, psd.promosalesprice, psd.vatamountsim, psd.vatamount, psd.targetmarginrate, psd.stockqty, psd.marginfrgap, psd.ismodified, psd.ismodifiedfr, psd.margin, psd.marginfr, psd.marginfrrate, psd.marginfrratesim, psd.marginfrsim, psd.margingap, psd.marginrate, psd.marginrategap, psd.marginratesim, psd.marginsim, psd.revenue, psd.revenuefr, psd.revenuefrgap, psd.revenuefrsim, psd.revenuegap, psd.revenuesim, psd.unitmargin, psd.unitmargingap, psd.unitmarginsim, psd.avgcompsalesprice, psd.elasticitycoeff, psd.minsalesprice, psd.maxsalesprice, psd.mincompsalesprice, psd.maxcompsalesprice, psd.oldpurchaseprice2, psd.compnb, psd.oldpurchaseprice, psd.purchaseprice1sim, psd.purchaseprice2, psd.isalignable, psd.iscomp, psd.purchaseprice1, psd.svap, psd.priceindexsim, psd.priceindex, psd.svapfr, psd.analysisaxisarray, psd.detention, psd.salespricegap, psd.purchaseprice1simarray, psd.salesprice, psd.salespricegappct, psd.salespricepromoincl, psd.salespricepromoinclsim, psd.salespricesim, psd.salespricesimarray, psd.databucomparray, psd.databannercomparray, psd.ispricemodifiable, psd.internalarray, psd.pv_hyper, psd.pv_enseigne
  • Filter: (psd.bulevel = 3)
  • Rows Removed by Filter: 238
  • Buffers: shared hit=1,185
  • Worker 0: actual time=0.030..2.755 rows=376 loops=1
  • Buffers: shared hit=528
8. 0.418 0.418 ↑ 1.0 1 836 / 2

Function Scan on pg_catalog.jsonb_to_record salespricesimarray2 (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=836)

  • Output: salespricesimarray2.simulatedsalesprice, salespricesimarray2.initialsalesprice
  • Function Call: jsonb_to_record((psd.salespricesimarray)[8])
  • Worker 0: actual time=0.001..0.001 rows=1 loops=376
9. 0.000 0.000 ↑ 1.0 1 836 / 2

Function Scan on pg_catalog.jsonb_to_record purchaseprice1simarray2 (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=836)

  • Output: purchaseprice1simarray2.simulatedsalesprice, purchaseprice1simarray2.initialsalesprice
  • Function Call: jsonb_to_record((psd.purchaseprice1simarray)[8])
  • Worker 0: actual time=0.000..0.000 rows=1 loops=376
10. 8.360 8.360 ↑ 1.0 6 836 / 2

Index Only Scan using product_nomenclature_link_perf_idx on pricing.product_nomenclature_link pnl (cost=0.43..7.14 rows=6 width=16) (actual time=0.007..0.020 rows=6 loops=836)

  • Output: pnl.productid, pnl.nomenclatureid, pnl.level, pnl.nomenclaturecode
  • Index Cond: (pnl.productid = psd.productid)
  • Heap Fetches: 5,016
  • Buffers: shared hit=7,560
  • Worker 0: actual time=0.011..0.034 rows=6 loops=376
  • Buffers: shared hit=3,405
11. 0.069 0.170 ↓ 1.0 472 1

Hash (cost=13.25..13.25 rows=469 width=16) (actual time=0.170..0.170 rows=472 loops=1)

  • Output: bsl.bustructurecode, bsl.bustructureid, bsl.level, bsl.buid
  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
  • Buffers: shared hit=5
12. 0.101 0.101 ↓ 1.0 472 1

Seq Scan on pricing.bu_structure_link bsl (cost=0.00..13.25 rows=469 width=16) (actual time=0.014..0.101 rows=472 loops=1)

  • Output: bsl.bustructurecode, bsl.bustructureid, bsl.level, bsl.buid
  • Filter: ((bsl.level >= 2) AND (bsl.bustructurecode = 1))
  • Rows Removed by Filter: 98
  • Buffers: shared hit=5
Planning time : 0.569 ms
Execution time : 27.029 ms