explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ug1T

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 0.878 ↓ 0.0 0 1

Hash Join (cost=959.11..979.31 rows=30 width=3,883) (actual time=0.878..0.878 rows=0 loops=1)

  • Hash Cond: (s.load_id = q.id)
2.          

CTE q

3. 0.001 0.077 ↓ 0.0 0 1

WindowAgg (cost=901.59..936.76 rows=2,010 width=265) (actual time=0.077..0.077 rows=0 loops=1)

4. 0.015 0.076 ↓ 0.0 0 1

Sort (cost=901.59..906.61 rows=2,010 width=257) (actual time=0.076..0.076 rows=0 loops=1)

  • Sort Key: l.internal_reference_id
  • Sort Method: quicksort Memory: 25kB
5. 0.035 0.061 ↓ 0.0 0 1

Hash Join (cost=175.84..791.31 rows=2,010 width=257) (actual time=0.061..0.061 rows=0 loops=1)

  • Hash Cond: (lf.load_id = l.id)
6. 0.021 0.021 ↑ 4,463.0 1 1

Seq Scan on load_fact lf (cost=0.00..578.63 rows=4,463 width=142) (actual time=0.021..0.021 rows=1 loops=1)

7. 0.002 0.005 ↓ 0.0 0 1

Hash (cost=162.09..162.09 rows=1,100 width=131) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
8. 0.000 0.003 ↓ 0.0 0 1

Hash Join (cost=114.97..162.09 rows=1,100 width=131) (actual time=0.003..0.003 rows=0 loops=1)

  • Hash Cond: (tl.id = l.id)
9. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on temp_load tl (cost=0.00..32.00 rows=1,100 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: should_return
10. 0.000 0.000 ↓ 0.0 0

Hash (cost=84.43..84.43 rows=2,443 width=123) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on load l (cost=0.00..84.43 rows=2,443 width=123) (never executed)

12.          

CTE paged_loads

13. 0.002 0.079 ↓ 0.0 0 1

Limit (cost=0.00..2.02 rows=30 width=3,795) (actual time=0.079..0.079 rows=0 loops=1)

14. 0.077 0.077 ↓ 0.0 0 1

CTE Scan on q q_1 (cost=0.00..45.23 rows=670 width=3,795) (actual time=0.077..0.077 rows=0 loops=1)

  • Filter: (row_number > 30)
15.          

CTE stops

16. 0.000 0.873 ↓ 0.0 0 1

HashAggregate (cost=15.85..19.35 rows=200 width=72) (actual time=0.872..0.873 rows=0 loops=1)

  • Group Key: s_1.parent_id
17.          

Initplan (forHashAggregate)

18. 0.080 0.080 ↓ 0.0 0 1

CTE Scan on paged_loads l_1 (cost=0.00..0.60 rows=30 width=8) (actual time=0.080..0.080 rows=0 loops=1)

19. 0.869 0.869 ↓ 0.0 0 1

Function Scan on isp_get_all_by_parents s_1 (cost=0.25..10.25 rows=1,000 width=5,667) (actual time=0.869..0.869 rows=0 loops=1)

20. 0.875 0.875 ↓ 0.0 0 1

CTE Scan on stops s (cost=0.00..4.00 rows=200 width=40) (actual time=0.875..0.875 rows=0 loops=1)

21. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.60..0.60 rows=30 width=3,763) (never executed)

22. 0.000 0.000 ↓ 0.0 0

CTE Scan on paged_loads q (cost=0.00..0.60 rows=30 width=3,763) (never executed)

Planning time : 4.256 ms
Execution time : 1.979 ms