explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2YYc

Settings
# exclusive inclusive rows x rows loops node
1. 31.834 31.834 ↑ 143.5 48 1

CTE Scan on vcalcul v (cost=24,099.81..24,237.57 rows=6,888 width=180) (actual time=31.525..31.834 rows=48 loops=1)

  • Buffers: shared hit=18364
2.          

CTE vselect

3. 0.000 31.720 ↑ 143.5 48 1

Finalize GroupAggregate (cost=22,756.65..23,893.17 rows=6,888 width=136) (actual time=31.515..31.720 rows=48 loops=1)

  • Group Key: psd.bustructureid, psd.bulevel, n.nomenclatureid, n.level
  • Buffers: shared hit=18364
4. 0.000 33.628 ↑ 74.1 93 1

Gather Merge (cost=22,756.65..23,548.77 rows=6,888 width=128) (actual time=31.497..33.628 rows=93 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=26223
5. 0.100 55.332 ↑ 149.7 46 2

Sort (cost=21,756.64..21,773.86 rows=6,888 width=128) (actual time=27.663..27.666 rows=46 loops=2)

  • Sort Key: psd.bustructureid, n.nomenclatureid, n.level
  • Sort Method: quicksort Memory: 37kB
  • Worker 0: Sort Method: quicksort Memory: 36kB
  • Buffers: shared hit=26223
6. 10.522 55.232 ↑ 149.7 46 2

Partial HashAggregate (cost=21,196.99..21,317.53 rows=6,888 width=128) (actual time=27.526..27.616 rows=46 loops=2)

  • Group Key: psd.bustructureid, psd.bulevel, n.nomenclatureid, n.level
  • Buffers: shared hit=26216
7. 4.474 44.710 ↑ 1.2 8,645 2

Hash Join (cost=910.41..20,895.94 rows=10,035 width=99) (actual time=4.401..22.355 rows=8,645 loops=2)

  • Hash Cond: (pnl.nomenclatureid = n.nomenclatureid)
  • Buffers: shared hit=26216
8. 2.402 32.260 ↑ 1.2 8,645 2

Nested Loop (cost=0.43..19,959.61 rows=10,035 width=95) (actual time=0.364..16.130 rows=8,645 loops=2)

  • Buffers: shared hit=24703
9. 3.648 5.652 ↑ 1.2 1,729 2

Nested Loop (cost=0.00..1,467.11 rows=2,034 width=95) (actual time=0.330..2.826 rows=1,729 loops=2)

  • Buffers: shared hit=1401
10. 2.004 2.004 ↑ 1.2 1,729 2

Parallel Seq Scan on price_strategy_detail_3187 psd (cost=0.00..1,426.43 rows=2,034 width=263) (actual time=0.319..1.002 rows=1,729 loops=2)

  • Filter: (bulevel = 3)
  • Buffers: shared hit=1401
11. 0.000 0.000 ↑ 1.0 1 3,458

Function Scan on jsonb_to_record spsimp (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=3,458)

12. 24.206 24.206 ↑ 1.0 5 3,458

Index Only Scan using product_nomenclature_link_perf_idx on product_nomenclature_link pnl (cost=0.43..9.04 rows=5 width=8) (actual time=0.003..0.007 rows=5 loops=3,458)

  • Index Cond: (productid = psd.productid)
  • Heap Fetches: 10205
  • Buffers: shared hit=23302
13. 2.096 7.976 ↑ 1.0 6,888 2

Hash (cost=823.88..823.88 rows=6,888 width=8) (actual time=3.987..3.988 rows=6,888 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 334kB
  • Buffers: shared hit=1510
14. 5.880 5.880 ↑ 1.0 6,888 2

Seq Scan on nomenclature n (cost=0.00..823.88 rows=6,888 width=8) (actual time=0.010..2.940 rows=6,888 loops=2)

  • Buffers: shared hit=1510
15.          

CTE vcalcul

16. 31.800 31.800 ↑ 143.5 48 1

CTE Scan on vselect v_1 (cost=0.00..206.64 rows=6,888 width=168) (actual time=31.521..31.800 rows=48 loops=1)

  • Buffers: shared hit=18364
Planning time : 0.544 ms
Execution time : 34.094 ms