explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hsir

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 21.948 427.287 ↑ 355.8 45 1

HashAggregate (cost=8,467.38..8,827.65 rows=16,012 width=220) (actual time=427.131..427.287 rows=45 loops=1)

  • Output: bsl.bustructureid, bsd.level, pnl.nomenclatureid, pnl.level, sum(psd.marginfrsim), sum(CASE WHEN (psd.purchaseprice1simarray[8] IS NOT NULL) THEN CASE WHEN (((psd.purchaseprice1simarray[8] ->> 'simulatedsalesprice'::text))::numeric < ((psd.purchaseprice1simarray[8] ->> 'initialsalesprice'::text))::numeric) THEN 1 ELSE 0 END ELSE 0 END), sum(CASE WHEN (psd.salespricesimarray[8] IS NOT NULL) THEN CASE WHEN (((psd.salespricesimarray[8] ->> 'simulatedsalesprice'::text))::numeric < ((psd.salespricesimarray[8] ->> 'initialsalesprice'::text))::numeric) THEN 1 ELSE 0 END ELSE 0 END), sum(CASE WHEN (psd.purchaseprice1simarray[8] IS NOT NULL) THEN CASE WHEN (((psd.purchaseprice1simarray[8] ->> 'simulatedsalesprice'::text))::numeric > ((psd.purchaseprice1simarray[8] ->> 'initialsalesprice'::text))::numeric) THEN 1 ELSE 0 END ELSE 0 END), sum(CASE WHEN (psd.salespricesimarray[8] IS NOT NULL) THEN CASE WHEN (((psd.salespricesimarray[8] ->> 'simulatedsalesprice'::text))::numeric > ((psd.salespricesimarray[8] ->> 'initialsalesprice'::text))::numeric) 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.bustructureid, bsd.level, pnl.nomenclatureid, pnl.level
  • Buffers: shared hit=7,773 read=980
  • I/O Timings: read=639.119
2. 1.303 405.339 ↑ 1.6 10,032 1

Hash Join (cost=1,027.86..6,706.06 rows=16,012 width=494) (actual time=31.945..405.339 rows=10,032 loops=1)

  • Output: bsl.bustructureid, bsd.level, pnl.nomenclatureid, pnl.level, psd.marginfrsim, psd.purchaseprice1simarray, psd.salespricesimarray, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim
  • Hash Cond: (psd.bustructureid = bsl.buid)
  • Buffers: shared hit=7,773 read=980
  • I/O Timings: read=639.119
3. 47.326 398.462 ↓ 1.0 5,016 1

Gather (cost=1,000.71..6,452.42 rows=4,832 width=490) (actual time=26.361..398.462 rows=5,016 loops=1)

  • Output: psd.marginfrsim, psd.purchaseprice1simarray, psd.salespricesimarray, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim, psd.bustructureid, pnl.nomenclatureid, pnl.level
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=7,773 read=972
  • I/O Timings: read=633.875
4. 1.535 351.136 ↑ 1.1 2,508 2 / 2

Nested Loop (cost=0.71..4,969.22 rows=2,842 width=490) (actual time=18.563..351.136 rows=2,508 loops=2)

  • Output: psd.marginfrsim, psd.purchaseprice1simarray, psd.salespricesimarray, psd.qtysim, psd.revenuesim, psd.revenuefrsim, psd.marginsim, psd.vatamountsim, psd.bustructureid, pnl.nomenclatureid, pnl.level
  • Buffers: shared hit=7,773 read=972
  • I/O Timings: read=633.875
  • Worker 0: actual time=12.301..308.501 rows=2,136 loops=1
  • Buffers: shared hit=3,333 read=410
  • I/O Timings: read=271.106
5. 202.465 202.465 ↑ 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=16.386..202.465 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=652 read=533
  • I/O Timings: read=381.734
  • Worker 0: actual time=10.986..172.686 rows=356 loops=1
  • Buffers: shared hit=297 read=224
  • I/O Timings: read=159.071
6. 147.136 147.136 ↑ 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.319..0.352 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,121 read=439
  • I/O Timings: read=252.142
  • Worker 0: actual time=0.335..0.378 rows=6 loops=356
  • Buffers: shared hit=3,036 read=186
  • I/O Timings: read=112.035
7. 0.048 5.574 ↑ 1.5 314 1

Hash (cost=21.20..21.20 rows=476 width=12) (actual time=5.574..5.574 rows=314 loops=1)

  • Output: bsl.bustructureid, bsl.buid, bsd.level
  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared read=8
  • I/O Timings: read=5.244
8. 0.103 5.526 ↑ 1.5 314 1

Hash Join (cost=8.05..21.20 rows=476 width=12) (actual time=2.771..5.526 rows=314 loops=1)

  • Output: bsl.bustructureid, bsl.buid, bsd.level
  • Inner Unique: true
  • Hash Cond: (bsl.bustructureid = bsd.bustructureid)
  • Buffers: shared read=8
  • I/O Timings: read=5.244
9. 4.788 4.788 ↑ 1.0 473 1

Seq Scan on pricing.bu_structure_link bsl (cost=0.00..11.88 rows=473 width=12) (actual time=2.077..4.788 rows=473 loops=1)

  • Output: bsl.bustructurecode, bsl.bustructureid, bsl.buid, bsl.level, bsl.creationdate, bsl.modificationdate, bsl.suppressiondate
  • Filter: (bsl.bustructurecode = 1)
  • Rows Removed by Filter: 97
  • Buffers: shared read=5
  • I/O Timings: read=4.694
10. 0.037 0.635 ↓ 1.0 158 1

Hash (cost=6.15..6.15 rows=152 width=12) (actual time=0.634..0.635 rows=158 loops=1)

  • Output: bsd.level, bsd.bustructureid, bsd.bustructurecode
  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared read=3
  • I/O Timings: read=0.550
11. 0.598 0.598 ↓ 1.0 158 1

Seq Scan on pricing.bu_structure_detail bsd (cost=0.00..6.15 rows=152 width=12) (actual time=0.545..0.598 rows=158 loops=1)

  • Output: bsd.level, bsd.bustructureid, bsd.bustructurecode
  • Filter: ((bsd.level >= 2) AND (bsd.bustructurecode = 1))
  • Rows Removed by Filter: 59
  • Buffers: shared read=3
  • I/O Timings: read=0.550
Planning time : 24.977 ms
Execution time : 429.558 ms