explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k5cE

Settings
# exclusive inclusive rows x rows loops node
1. 183.812 183.812 ↑ 1.2 780 1

CTE Scan on vcalcul v (cost=144,375.79..144,393.79 rows=900 width=420) (actual time=180.047..183.812 rows=780 loops=1)

  • Buffers: shared hit=18076 read=125
  • I/O Timings: read=0.909
2.          

CTE vselect1

3. 3.169 170.909 ↑ 1.9 432 1

Finalize GroupAggregate (cost=1,000.46..143,947.90 rows=840 width=224) (actual time=10.682..170.909 rows=432 loops=1)

  • Group Key: psd.bustructureid, psd.bulevel, psd.nomenclatureparentid
  • Buffers: shared hit=17079 read=125
  • I/O Timings: read=0.909
4. 0.000 167.740 ↑ 2.7 948 1

Gather Merge (cost=1,000.46..143,815.60 rows=2,520 width=216) (actual time=10.458..167.740 rows=948 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=88220 read=645
  • I/O Timings: read=5.905
5. 221.672 557.924 ↑ 3.5 237 4

Partial GroupAggregate (cost=0.42..142,519.46 rows=840 width=216) (actual time=1.889..139.481 rows=237 loops=4)

  • Group Key: psd.bustructureid, psd.bulevel, psd.nomenclatureparentid
  • Buffers: shared hit=88220 read=645
  • I/O Timings: read=5.905
6. 336.252 336.252 ↑ 1.3 41,942 4

Parallel Index Scan using idx_temp_2345 on price_strategy_detail_2345 psd (cost=0.42..136,682.76 rows=54,119 width=308) (actual time=0.045..84.063 rows=41,942 loops=4)

  • Buffers: shared hit=88220 read=645
  • I/O Timings: read=5.905
7.          

CTE vselect

8. 4.426 181.289 ↑ 1.2 780 1

HashAggregate (cost=364.89..396.39 rows=900 width=344) (actual time=180.034..181.289 rows=780 loops=1)

  • Group Key: uba.uba, n2.level, bsl.bustructureid, bsl.level
  • Buffers: shared hit=18076 read=125
  • I/O Timings: read=0.909
9. 0.827 176.863 ↓ 4.8 4,320 1

Hash Join (cost=60.52..333.39 rows=900 width=216) (actual time=174.074..176.863 rows=4,320 loops=1)

  • Hash Cond: (psd_1.bustructureid = bsl.buid)
  • Buffers: shared hit=18076 read=125
  • I/O Timings: read=0.909
10. 1.163 175.872 ↓ 3.6 1,440 1

Hash Join (cost=41.10..297.60 rows=400 width=212) (actual time=173.903..175.872 rows=1,440 loops=1)

  • Hash Cond: (n2.nomenclatureid = uba.uba)
  • Buffers: shared hit=18071 read=125
  • I/O Timings: read=0.909
11. 0.828 0.828 ↑ 1.0 7,114 1

Seq Scan on nomenclature n2 (cost=0.00..199.14 rows=7,114 width=8) (actual time=0.005..0.828 rows=7,114 loops=1)

  • Buffers: shared hit=128
12. 0.519 173.881 ↓ 3.6 1,440 1

Hash (cost=36.10..36.10 rows=400 width=208) (actual time=173.881..173.881 rows=1,440 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 202kB
  • Buffers: shared hit=17943 read=125
  • I/O Timings: read=0.909
13. 0.448 173.362 ↓ 3.6 1,440 1

Nested Loop (cost=0.28..36.10 rows=400 width=208) (actual time=79.168..173.362 rows=1,440 loops=1)

  • Buffers: shared hit=17943 read=125
  • I/O Timings: read=0.909
14. 0.409 172.338 ↓ 72.0 288 1

Nested Loop (cost=0.28..28.10 rows=4 width=248) (actual time=79.157..172.338 rows=288 loops=1)

  • Buffers: shared hit=17943 read=125
  • I/O Timings: read=0.909
15. 171.353 171.353 ↓ 72.0 288 1

CTE Scan on vselect1 psd_1 (cost=0.00..18.90 rows=4 width=212) (actual time=79.137..171.353 rows=288 loops=1)

  • Filter: (bulevel = 3)
  • Rows Removed by Filter: 144
  • Buffers: shared hit=17079 read=125
  • I/O Timings: read=0.909
16. 0.576 0.576 ↑ 1.0 1 288

Index Scan using nomenclature_pk on nomenclature n1 (cost=0.28..2.30 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=288)

  • Index Cond: (nomenclatureid = psd_1.nomenclatureid)
  • Buffers: shared hit=864
17. 0.576 0.576 ↑ 20.0 5 288

Function Scan on unnest uba (cost=0.00..1.00 rows=100 width=4) (actual time=0.002..0.002 rows=5 loops=288)

18. 0.064 0.164 ↓ 1.0 472 1

Hash (cost=13.55..13.55 rows=469 width=12) (actual time=0.164..0.164 rows=472 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
  • Buffers: shared hit=5
19. 0.100 0.100 ↓ 1.0 472 1

Seq Scan on bu_structure_link bsl (cost=0.00..13.55 rows=469 width=12) (actual time=0.009..0.100 rows=472 loops=1)

  • Filter: ((level >= 2) AND (bustructurecode = 1))
  • Rows Removed by Filter: 98
  • Buffers: shared hit=5
20.          

CTE vcalcul

21. 183.091 183.091 ↑ 1.2 780 1

CTE Scan on vselect v_1 (cost=0.00..31.50 rows=900 width=408) (actual time=180.043..183.091 rows=780 loops=1)

  • Buffers: shared hit=18076 read=125
  • I/O Timings: read=0.909
Planning time : 0.799 ms
Execution time : 184.185 ms