explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4AOb

Settings
# exclusive inclusive rows x rows loops node
1. 4,212.787 4,212.787 ↑ 2,651.0 2,314 1

CTE Scan on vcalcul v (cost=1,732,085.17..1,854,775.27 rows=6,134,505 width=360) (actual time=4,206.056..4,212.787 rows=2,314 loops=1)

2.          

CTE vselect

3. 29.191 1,047.658 ↑ 53.2 2,314 1

Finalize GroupAggregate (cost=212,602.16..342,088.84 rows=123,066 width=220) (actual time=744.587..1,047.658 rows=2,314 loops=1)

  • Group Key: pnl.nomenclatureid, pnl.level, psd.bustructureid, bu_structure_link.level
4. 0.000 1,018.467 ↑ 50.3 12,229 1

Gather Merge (cost=212,602.16..310,091.68 rows=615,330 width=220) (actual time=721.363..1,018.467 rows=12,229 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
5. 1,413.762 5,679.804 ↑ 60.4 2,038 6

Partial GroupAggregate (cost=211,602.08..234,990.60 rows=123,066 width=220) (actual time=685.491..946.634 rows=2,038 loops=6)

  • Group Key: pnl.nomenclatureid, pnl.level, psd.bustructureid, bu_structure_link.level
6. 1,504.422 4,266.042 ↑ 1.2 322,443 6

Sort (cost=211,602.08..212,583.96 rows=392,753 width=178) (actual time=665.223..711.007 rows=322,443 loops=6)

  • Sort Key: pnl.nomenclatureid, pnl.level, psd.bustructureid, bu_structure_link.level
  • Sort Method: quicksort Memory: 65153kB
  • Worker 0: Sort Method: quicksort Memory: 53577kB
  • Worker 1: Sort Method: quicksort Memory: 51228kB
  • Worker 2: Sort Method: quicksort Memory: 54040kB
  • Worker 3: Sort Method: quicksort Memory: 56421kB
  • Worker 4: Sort Method: quicksort Memory: 56244kB
7. 359.934 2,761.620 ↑ 1.2 322,443 6

Hash Join (cost=19.97..175,108.92 rows=392,753 width=178) (actual time=12.663..460.270 rows=322,443 loops=6)

  • Hash Cond: (psd.bustructureid = bu_structure_link.buid)
8. 124.026 2,400.408 ↑ 1.1 107,481 6

Nested Loop (cost=0.86..169,498.98 rows=120,967 width=174) (actual time=12.371..400.068 rows=107,481 loops=6)

9. 90.312 771.648 ↑ 1.2 17,914 6

Nested Loop (cost=0.42..145,749.16 rows=20,929 width=166) (actual time=12.307..128.608 rows=17,914 loops=6)

10. 169.338 681.336 ↑ 1.2 17,914 6

Nested Loop (cost=0.42..145,330.58 rows=20,929 width=134) (actual time=12.299..113.556 rows=17,914 loops=6)

11. 511.998 511.998 ↑ 1.2 17,914 6

Parallel Index Scan using price_strategy_detail_2345_fk1 on price_strategy_detail_2345 psd (cost=0.42..144,912.00 rows=20,929 width=300) (actual time=12.279..85.333 rows=17,914 loops=6)

  • Filter: (bulevel = 3)
  • Rows Removed by Filter: 10048
12. 0.000 0.000 ↑ 1.0 1 107,481

Function Scan on jsonb_to_record salespricesimarray2 (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=107,481)

13. 0.000 0.000 ↑ 1.0 1 107,481

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

14. 1,504.734 1,504.734 ↑ 1.0 6 107,481

Index Scan using product_nomenclature_link_fk1 on product_nomenclature_link pnl (cost=0.43..1.07 rows=6 width=16) (actual time=0.005..0.014 rows=6 loops=107,481)

  • Index Cond: ((nomenclaturecode = 4) AND (productid = psd.productid))
15. 0.462 1.278 ↓ 1.0 472 6

Hash (cost=13.25..13.25 rows=469 width=8) (actual time=0.213..0.213 rows=472 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
16. 0.816 0.816 ↓ 1.0 472 6

Seq Scan on bu_structure_link (cost=0.00..13.25 rows=469 width=8) (actual time=0.025..0.136 rows=472 loops=6)

  • Filter: ((level >= 2) AND (bustructurecode = 1))
  • Rows Removed by Filter: 98
17.          

CTE vselectarray

18. 0.000 3,150.963 ↑ 573.6 3,476 1

Finalize GroupAggregate (cost=460,913.23..876,609.07 rows=1,993,891 width=80) (actual time=1,783.321..3,150.963 rows=3,476 loops=1)

  • Group Key: bsl.bustructureid, bsd.level, n.nomenclatureid, n.level
19. 0.000 3,154.846 ↑ 126.8 15,721 1

Gather Merge (cost=460,913.23..786,883.95 rows=1,993,892 width=112) (actual time=1,672.480..3,154.846 rows=15,721 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
20. 6,186.905 14,013.595 ↑ 158.5 3,144 5

Partial GroupAggregate (cost=459,913.18..548,392.13 rows=498,473 width=112) (actual time=1,584.888..2,802.719 rows=3,144 loops=5)

  • Group Key: bsl.bustructureid, bsd.level, n.nomenclatureid, n.level
21. 3,534.110 7,826.690 ↑ 1.4 355,061 5

Sort (cost=459,913.18..461,159.36 rows=498,473 width=59) (actual time=1,477.844..1,565.338 rows=355,061 loops=5)

  • Sort Key: bsl.bustructureid, bsd.level, n.nomenclatureid, n.level
  • Sort Method: external merge Disk: 106640kB
  • Worker 0: Sort Method: external merge Disk: 100144kB
  • Worker 1: Sort Method: external merge Disk: 98296kB
  • Worker 2: Sort Method: external merge Disk: 83760kB
  • Worker 3: Sort Method: external merge Disk: 88184kB
22. 405.075 4,292.580 ↑ 1.4 355,061 5

Hash Join (cost=353,595.71..412,739.80 rows=498,473 width=59) (actual time=287.655..858.516 rows=355,061 loops=5)

  • Hash Cond: (pnl_1.nomenclatureid = n.nomenclatureid)
23. 1,862.045 3,871.500 ↑ 1.4 355,061 5

Parallel Hash Join (cost=353,307.64..411,142.46 rows=498,473 width=59) (actual time=284.355..774.300 rows=355,061 loops=5)

  • Hash Cond: (pnl_1.productid = price_strategy_detail_2345.productid)
24. 591.810 591.810 ↑ 1.3 897,098 5

Parallel Seq Scan on product_nomenclature_link pnl_1 (cost=0.00..48,593.73 rows=1,121,373 width=12) (actual time=0.011..118.362 rows=897,098 loops=5)

25. 273.665 1,417.645 ↓ 1.0 59,177 5

Parallel Hash (cost=352,588.97..352,588.97 rows=57,494 width=55) (actual time=283.529..283.529 rows=59,177 loops=5)

  • Buckets: 524288 Batches: 1 Memory Usage: 90016kB
26. 63.270 1,143.980 ↓ 1.0 59,177 5

Hash Join (cost=27.15..352,588.97 rows=57,494 width=55) (actual time=104.575..228.796 rows=59,177 loops=5)

  • Hash Cond: (price_strategy_detail_2345.bustructureid = bsl.buid)
27. 302.335 1,078.780 ↑ 58.9 29,588 5

ProjectSet (cost=0.00..346,628.32 rows=1,744,000 width=1,220) (actual time=104.167..215.756 rows=29,588 loops=5)

28. 776.445 776.445 ↓ 1.2 21,496 5

Parallel Seq Scan on price_strategy_detail_2345 (cost=0.00..337,559.52 rows=17,440 width=1,259) (actual time=104.144..155.289 rows=21,496 loops=5)

  • Filter: (bulevel = 3)
  • Rows Removed by Filter: 12057
29. 0.335 1.930 ↑ 1.5 314 5

Hash (cost=21.20..21.20 rows=476 width=12) (actual time=0.386..0.386 rows=314 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
30. 0.530 1.595 ↑ 1.5 314 5

Hash Join (cost=8.05..21.20 rows=476 width=12) (actual time=0.187..0.319 rows=314 loops=5)

  • Hash Cond: (bsl.bustructureid = bsd.bustructureid)
31. 0.550 0.550 ↑ 1.0 473 5

Seq Scan on bu_structure_link bsl (cost=0.00..11.88 rows=473 width=12) (actual time=0.028..0.110 rows=473 loops=5)

  • Filter: (bustructurecode = 1)
  • Rows Removed by Filter: 97
32. 0.195 0.515 ↓ 1.0 158 5

Hash (cost=6.15..6.15 rows=152 width=12) (actual time=0.103..0.103 rows=158 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
33. 0.320 0.320 ↓ 1.0 158 5

Seq Scan on bu_structure_detail bsd (cost=0.00..6.15 rows=152 width=12) (actual time=0.019..0.064 rows=158 loops=5)

  • Filter: ((level >= 2) AND (bustructurecode = 1))
  • Rows Removed by Filter: 59
34. 5.590 16.005 ↑ 1.0 7,114 5

Hash (cost=199.14..199.14 rows=7,114 width=8) (actual time=3.201..3.201 rows=7,114 loops=5)

  • Buckets: 8192 Batches: 1 Memory Usage: 342kB
35. 10.415 10.415 ↑ 1.0 7,114 5

Seq Scan on nomenclature n (cost=0.00..199.14 rows=7,114 width=8) (actual time=0.025..2.083 rows=7,114 loops=5)

36.          

CTE vcalcul

37. 4.498 4,210.917 ↑ 2,651.0 2,314 1

Merge Join (cost=308,108.13..513,387.27 rows=6,134,505 width=348) (actual time=4,206.051..4,210.917 rows=2,314 loops=1)

  • Merge Cond: ((v_1.bustructureid = va.bustructureid) AND (v_1.nomenclatureid = va.nomenclatureid))
38. 1.525 1,052.010 ↑ 53.2 2,314 1

Sort (cost=12,865.98..13,173.64 rows=123,066 width=220) (actual time=1,051.852..1,052.010 rows=2,314 loops=1)

  • Sort Key: v_1.bustructureid, v_1.nomenclatureid
  • Sort Method: quicksort Memory: 436kB
39. 1,050.485 1,050.485 ↑ 53.2 2,314 1

CTE Scan on vselect v_1 (cost=0.00..2,461.32 rows=123,066 width=220) (actual time=744.591..1,050.485 rows=2,314 loops=1)

40. 0.358 3,154.409 ↑ 573.6 3,476 1

Materialize (cost=295,242.15..305,211.61 rows=1,993,891 width=72) (actual time=3,153.861..3,154.409 rows=3,476 loops=1)

41. 1.252 3,154.051 ↑ 573.6 3,476 1

Sort (cost=295,242.15..300,226.88 rows=1,993,891 width=72) (actual time=3,153.858..3,154.051 rows=3,476 loops=1)

  • Sort Key: va.bustructureid, va.nomenclatureid
  • Sort Method: quicksort Memory: 362kB
42. 3,152.799 3,152.799 ↑ 573.6 3,476 1

CTE Scan on vselectarray va (cost=0.00..39,877.82 rows=1,993,891 width=72) (actual time=1,783.324..3,152.799 rows=3,476 loops=1)

Planning time : 1.536 ms
Execution time : 4,273.491 ms