explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q1kJ

Settings
# exclusive inclusive rows x rows loops node
1. 794.042 794.042 ↓ 16.1 4,455 1

CTE Scan on vcalcul (cost=41,367.90..41,373.44 rows=277 width=40) (actual time=143.144..794.042 rows=4,455 loops=1)

  • Output: vcalcul.productid, vcalcul.buid, vcalcul.calculatedprice
2.          

CTE vcalcul

3. 2.663 791.868 ↓ 16.1 4,455 1

Nested Loop (cost=2,816.43..41,367.90 rows=277 width=40) (actual time=143.142..791.868 rows=4,455 loops=1)

  • Output: psd.productid, bsl.buid, (check_rule_constraints(CASE WHEN ((psd.salespricesim <> '0'::numeric) AND (psd.oldpurchaseprice <> '0'::numeric)) THEN ((psd.purchaseprice1 / ('1'::numeric - (((psd.salespricesim / ('1'::numeric + psd.vatrate)) - psd.oldpurchaseprice) / (psd.salespricesim / ('1'::numeric + psd.vatrate))))) * ('1'::numeric + psd.vatrate)) ELSE psd.salespricesim END, psd.salespricesim, '{"psychoruleid": 1, "issrpcontroled": false, "roundingruleid": 1, "ispricemodifiable": true, "ispromotionexcluded": true, "isupchangepriceallowed": true, "iscomppromotionexcluded": true, "isdownchangepriceallowed": false}'::jsonb, psd.svap, 2, psd.salesprice))
4. 153.700 780.295 ↓ 153.6 4,455 1

Nested Loop (cost=2,816.16..41,323.37 rows=29 width=40) (actual time=143.131..780.295 rows=4,455 loops=1)

  • Output: psd.productid, psd.bustructureid, check_rule_constraints(CASE WHEN ((psd.salespricesim <> '0'::numeric) AND (psd.oldpurchaseprice <> '0'::numeric)) THEN ((psd.purchaseprice1 / ('1'::numeric - (((psd.salespricesim / ('1'::numeric + psd.vatrate)) - psd.oldpurchaseprice) / (psd.salespricesim / ('1'::numeric + psd.vatrate))))) * ('1'::numeric + psd.vatrate)) ELSE psd.salespricesim END, psd.salespricesim, '{"psychoruleid": 1, "issrpcontroled": false, "roundingruleid": 1, "ispricemodifiable": true, "ispromotionexcluded": true, "isupchangepriceallowed": true, "iscomppromotionexcluded": true, "isdownchangepriceallowed": false}'::jsonb, psd.svap, 2, psd.salesprice)
  • Join Filter: (n.elementid1 = ((uaaa1.uaaa1 ->> 'elementid'::text))::integer)
  • Rows Removed by Join Filter: 136017
5. 34.923 342.691 ↓ 54.7 141,952 1

Hash Join (cost=2,816.15..40,744.57 rows=2,594 width=292) (actual time=133.991..342.691 rows=141,952 loops=1)

  • Output: psd.productid, psd.bustructureid, psd.salespricesim, psd.oldpurchaseprice, psd.purchaseprice1, psd.vatrate, psd.svap, psd.salesprice, psd.analysisaxisarray, n.elementid1
  • Hash Cond: (psd.bustructureid = bsl_1.buid)
6. 84.287 307.667 ↓ 58.6 181,936 1

Nested Loop (cost=2,788.06..40,651.74 rows=3,104 width=292) (actual time=133.884..307.667 rows=181,936 loops=1)

  • Output: psd.productid, psd.bustructureid, psd.salespricesim, psd.oldpurchaseprice, psd.purchaseprice1, psd.vatrate, psd.svap, psd.salesprice, psd.analysisaxisarray, n.elementid1
7. 2.257 137.000 ↓ 156.2 12,340 1

Hash Join (cost=2,787.63..2,895.59 rows=79 width=8) (actual time=133.865..137.000 rows=12,340 loops=1)

  • Output: psj.productid, n.elementid1
  • Hash Cond: (psj.nomenclatureid = n.nomenclatureid)
8. 0.898 0.898 ↓ 1.0 3,085 1

Index Scan using price_strategy_jobengine_947_fk2 on pricing.price_strategy_jobengine_947 psj (cost=0.42..96.20 rows=3,039 width=12) (actual time=0.013..0.898 rows=3,085 loops=1)

  • Output: psj.productid, psj.nomenclatureid, psj.seq, psj.nomenclatureparentid, psj.detention
  • Index Cond: ((psj.seq >= 260449) AND (psj.seq <= 263533))
9. 4.354 133.845 ↓ 376.8 33,536 1

Hash (cost=2,786.09..2,786.09 rows=89 width=8) (actual time=133.845..133.845 rows=33,536 loops=1)

  • Output: n.nomenclatureid, n.elementid1
  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1822kB
10. 3.674 129.491 ↓ 376.8 33,536 1

Subquery Scan on n (cost=2,784.31..2,786.09 rows=89 width=8) (actual time=118.163..129.491 rows=33,536 loops=1)

  • Output: n.nomenclatureid, n.elementid1
11. 5.642 125.817 ↓ 376.8 33,536 1

Unique (cost=2,784.31..2,785.20 rows=89 width=48) (actual time=118.160..125.817 rows=33,536 loops=1)

  • Output: n_1.nomenclatureid, (((sva.sva ->> 'elementid1'::text))::integer), (((sva.sva ->> 'elementid2'::text))::integer), NULL::text, n_1.level
12. 15.262 120.175 ↓ 376.8 33,536 1

Sort (cost=2,784.31..2,784.54 rows=89 width=48) (actual time=118.159..120.175 rows=33,536 loops=1)

  • Output: n_1.nomenclatureid, (((sva.sva ->> 'elementid1'::text))::integer), (((sva.sva ->> 'elementid2'::text))::integer), NULL::text, n_1.level
  • Sort Key: n_1.nomenclatureid, (((sva.sva ->> 'elementid1'::text))::integer), (((sva.sva ->> 'elementid2'::text))::integer)
  • Sort Method: quicksort Memory: 3109kB
13. 11.956 104.913 ↓ 376.8 33,536 1

Nested Loop (cost=3.44..2,781.43 rows=89 width=48) (actual time=0.171..104.913 rows=33,536 loops=1)

  • Output: n_1.nomenclatureid, ((sva.sva ->> 'elementid1'::text))::integer, ((sva.sva ->> 'elementid2'::text))::integer, NULL::text, n_1.level
14. 0.008 0.008 ↑ 1.0 1 1

Index Scan using scenario_rule_pk on pricing.scenario_rule sr (cost=0.28..2.30 rows=1 width=286) (actual time=0.006..0.008 rows=1 loops=1)

  • Output: sr.scenarioid, sr.versioncode, sr.rank, sr.label, sr.pricetreatmentid, sr.declinaisonrulecode, sr.standardvaluearray, sr.compvaluearray, sr.optimvaluearray, sr.buvaluearray, sr.filterconditionarray, sr.bustructurearray, sr.bucalcullevel, sr.constraintsarray, sr.creationdate, sr.modificationdate, sr.suppressiondate, sr.linkmasterdeterminationtypecode, sr.pricinglistarray
  • Index Cond: ((sr.scenarioid = 704) AND (sr.versioncode = 1) AND (sr.rank = 3))
15. 6.987 92.949 ↓ 376.8 33,536 1

Nested Loop (cost=3.16..2,776.90 rows=89 width=40) (actual time=0.162..92.949 rows=33,536 loops=1)

  • Output: sva.sva, n_1.nomenclatureid, n_1.level
16. 2.122 2.122 ↑ 1.5 8,384 1

Seq Scan on pricing.nomenclature n_1 (cost=0.00..757.27 rows=12,221 width=48) (actual time=0.128..2.122 rows=8,384 loops=1)

  • Output: n_1.nomenclaturecode, n_1.label, n_1.parentid, n_1.level, n_1.creationdate, n_1.modificationdate, n_1.suppressiondate, n_1.brancharray, n_1.externalcode, n_1.nomenclatureid
  • Filter: (n_1.level = 5)
  • Rows Removed by Filter: 1016
17. 25.152 83.840 ↓ 4.0 4 8,384

Hash Join (cost=3.16..3.32 rows=1 width=36) (actual time=0.008..0.010 rows=4 loops=8,384)

  • Output: sva.sva, nl.nomenclatureid
  • Inner Unique: true
  • Hash Cond: (((sva.sva ->> 'nomenclatureid'::text))::integer = nl.nomenclatureid)
18. 8.384 8.384 ↑ 2.5 4 8,384

Function Scan on pg_catalog.unnest sva (cost=0.00..0.13 rows=10 width=32) (actual time=0.000..0.001 rows=4 loops=8,384)

  • Output: sva.sva
  • Function Call: unnest(sr.standardvaluearray)
  • Filter: ((sva.sva ->> 'value'::text) IS NOT NULL)
19. 8.384 50.304 ↑ 2.0 5 8,384

Hash (cost=3.03..3.03 rows=10 width=4) (actual time=0.006..0.006 rows=5 loops=8,384)

  • Output: nl.nomenclatureid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 41.920 41.920 ↑ 2.0 5 8,384

Index Only Scan using nomenclature_pk on pricing.nomenclature nl (cost=0.29..3.03 rows=10 width=4) (actual time=0.001..0.005 rows=5 loops=8,384)

  • Output: nl.nomenclatureid
  • Index Cond: (nl.nomenclatureid = ANY (n_1.brancharray))
  • Heap Fetches: 0
21. 86.380 86.380 ↑ 43.2 15 12,340

Index Scan using price_strategy_detail_947_pk on pricing.price_strategy_detail_947 psd (cost=0.43..471.45 rows=648 width=288) (actual time=0.002..0.007 rows=15 loops=12,340)

  • Output: psd.productid, psd.bustructureid, psd.nomenclatureid, psd.bulevel, psd.nomenclatureparentid, psd.promosalesprice, psd.activite, psd.statut, psd.qtysim, psd.qty, psd.startpromodate, psd.mapka, psd.stockqty, psd.vatamountsim, psd.vatrate, psd.marginratemax, psd.marginratemin, psd.vatamount, psd.endpromodate, psd.unitmargin, psd.unitmarginsim, psd.ismodified, psd.unitmargingap, psd.margin, psd.margingap, psd.marginrate, psd.marginrategap, psd.marginratesim, psd.marginsim, psd.revenue, psd.revenuegap, psd.revenuesim, psd.avgcompsalesprice, psd.maxsalesprice, psd.minsalesprice, psd.mincompsalesprice, psd.maxcompsalesprice, psd.compnb, psd.oldpurchaseprice, psd.pairsnb, psd.purchaseprice1, psd.purchaseprice1sim, psd.iscomp, psd.isalignable, psd.wgtpriceindexsim, psd.priceindex, psd.priceindexsim, psd.svap, psd.wgtpriceindex, psd.analysisaxisarray, psd.detention, psd.salespricegappct, psd.salespricegap, psd.salesprice, psd.salespricesimarray, psd.salespricepromoincl, psd.salespricepromoinclsim, psd.salespricesim, psd.databucomparray, psd.databannercomparray, psd.internalarray, psd.ispricemodifiable
  • Index Cond: (psd.productid = psj.productid)
22. 0.017 0.101 ↓ 1.2 111 1

Hash (cost=26.93..26.93 rows=93 width=4) (actual time=0.101..0.101 rows=111 loops=1)

  • Output: bsl_1.buid
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
23. 0.084 0.084 ↓ 1.2 111 1

Index Only Scan using bu_structure_link_pk on pricing.bu_structure_link bsl_1 (cost=0.27..26.93 rows=93 width=4) (actual time=0.018..0.084 rows=111 loops=1)

  • Output: bsl_1.buid
  • Index Cond: ((bsl_1.bustructurecode = 2) AND (bsl_1.bustructureid = ANY ('{173}'::integer[])) AND (bsl_1.level = 4))
  • Heap Fetches: 111
24. 283.904 283.904 ↑ 1.0 1 141,952

Function Scan on pg_catalog.unnest uaaa1 (cost=0.00..0.20 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=141,952)

  • Output: uaaa1.uaaa1
  • Function Call: unnest(psd.analysisaxisarray)
  • Filter: (((uaaa1.uaaa1 ->> 'axisid'::text))::integer = 2)
  • Rows Removed by Filter: 3
25. 8.910 8.910 ↑ 4.0 1 4,455

Index Only Scan using bu_structure_link_pk on pricing.bu_structure_link bsl (cost=0.27..1.49 rows=4 width=8) (actual time=0.002..0.002 rows=1 loops=4,455)

  • Output: bsl.bustructurecode, bsl.bustructureid, bsl.level, bsl.buid
  • Index Cond: ((bsl.bustructurecode = 2) AND (bsl.bustructureid = psd.bustructureid) AND (bsl.level = 4))
  • Heap Fetches: 4455
Planning time : 1.327 ms
Execution time : 794.564 ms