explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 39G

Settings
# exclusive inclusive rows x rows loops node
1. 68.724 3,808.397 ↑ 125.1 122,695 1

Hash Join (cost=365,934.36..632,365.45 rows=15,355,250 width=16) (actual time=3,494.035..3,808.397 rows=122,695 loops=1)

  • Hash Cond: (ssy.seasonyearid = sy.seasonyearid)
2. 55.780 3,738.706 ↑ 25.0 122,695 1

Merge Join (cost=365,906.35..451,913.25 rows=3,071,050 width=32) (actual time=3,493.056..3,738.706 rows=122,695 loops=1)

  • Merge Cond: (((td.departmentid)::uuid) = (((sh.eav ->> 'eavdepartment'::text))::uuid))
3. 0.073 0.876 ↑ 17.5 57 1

Sort (cost=59.83..62.33 rows=1,000 width=32) (actual time=0.844..0.876 rows=57 loops=1)

  • Sort Key: ((td.departmentid)::uuid)
  • Sort Method: quicksort Memory: 33kB
4. 0.803 0.803 ↑ 17.5 57 1

Function Scan on regexp_split_to_table td (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.665..0.803 rows=57 loops=1)

5. 95.760 3,682.050 ↑ 5.0 122,695 1

Materialize (cost=365,846.52..368,917.57 rows=614,210 width=391) (actual time=3,492.203..3,682.050 rows=122,695 loops=1)

6. 451.574 3,586.290 ↑ 5.0 122,695 1

Sort (cost=365,846.52..367,382.05 rows=614,210 width=391) (actual time=3,492.194..3,586.290 rows=122,695 loops=1)

  • Sort Key: (((sh.eav ->> 'eavdepartment'::text))::uuid)
  • Sort Method: external merge Disk: 50192kB
7. 776.031 3,134.716 ↑ 5.0 122,695 1

Merge Join (cost=71,242.85..88,458.23 rows=614,210 width=391) (actual time=2,095.310..3,134.716 rows=122,695 loops=1)

  • Merge Cond: (((tds.devstageid)::uuid) = (((sh.eav ->> 'eavdevstage'::text))::uuid))
8. 0.012 0.058 ↑ 142.9 7 1

Sort (cost=59.83..62.33 rows=1,000 width=32) (actual time=0.053..0.058 rows=7 loops=1)

  • Sort Key: ((tds.devstageid)::uuid)
  • Sort Method: quicksort Memory: 25kB
9. 0.046 0.046 ↑ 142.9 7 1

Function Scan on regexp_split_to_table tds (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.032..0.046 rows=7 loops=1)

10. 174.630 2,358.627 ↑ 1.0 122,696 1

Materialize (cost=71,183.02..71,797.23 rows=122,842 width=391) (actual time=2,095.220..2,358.627 rows=122,696 loops=1)

11. 420.070 2,183.997 ↑ 1.0 122,696 1

Sort (cost=71,183.02..71,490.12 rows=122,842 width=391) (actual time=2,095.215..2,183.997 rows=122,696 loops=1)

  • Sort Key: (((sh.eav ->> 'eavdevstage'::text))::uuid)
  • Sort Method: external merge Disk: 50176kB
12. 1,130.327 1,763.927 ↑ 1.0 122,842 1

Hash Join (cost=26,306.12..38,962.41 rows=122,842 width=391) (actual time=634.680..1,763.927 rows=122,842 loops=1)

  • Hash Cond: (ssy.styleid = sh.styleid)
13. 44.797 44.797 ↑ 1.0 122,842 1

Seq Scan on pstyleseasonyear ssy (cost=0.00..3,419.42 rows=122,842 width=32) (actual time=0.029..44.797 rows=122,842 loops=1)

14. 228.533 588.803 ↑ 1.0 122,983 1

Hash (cost=18,762.83..18,762.83 rows=122,983 width=375) (actual time=588.803..588.803 rows=122,983 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 3227kB
15. 360.270 360.270 ↑ 1.0 122,983 1

Seq Scan on pstyleheader sh (cost=0.00..18,762.83 rows=122,983 width=375) (actual time=0.065..360.270 rows=122,983 loops=1)

16. 0.028 0.967 ↑ 5.0 49 1

Hash (cost=24.95..24.95 rows=245 width=48) (actual time=0.967..0.967 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
17. 0.385 0.939 ↑ 5.0 49 1

Hash Join (cost=2.11..24.95 rows=245 width=48) (actual time=0.574..0.939 rows=49 loops=1)

  • Hash Cond: ((tsy.seasonyearid)::uuid = sy.seasonyearid)
18. 0.467 0.467 ↑ 20.4 49 1

Function Scan on regexp_split_to_table tsy (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.463..0.467 rows=49 loops=1)

19. 0.018 0.087 ↑ 1.0 49 1

Hash (cost=1.49..1.49 rows=49 width=16) (actual time=0.087..0.087 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.069 0.069 ↑ 1.0 49 1

Seq Scan on pseasonyear sy (cost=0.00..1.49 rows=49 width=16) (actual time=0.064..0.069 rows=49 loops=1)

Planning time : 4.474 ms
Execution time : 3,853.814 ms