explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FAb2

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 241.870 ↑ 1.0 30 1

Hash Join (cost=717.18..722.23 rows=30 width=3,835) (actual time=241.216..241.870 rows=30 loops=1)

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

CTE q

3. 0.024 8.870 ↑ 37.5 30 1

WindowAgg (cost=675.16..694.83 rows=1,124 width=318) (actual time=8.844..8.870 rows=30 loops=1)

4. 0.778 8.846 ↑ 37.5 30 1

Sort (cost=675.16..677.97 rows=1,124 width=310) (actual time=8.837..8.846 rows=30 loops=1)

  • Sort Key: l.internal_reference_id DESC
  • Sort Method: quicksort Memory: 407kB
5. 1.306 8.068 ↑ 1.1 1,007 1

Hash Join (cost=286.97..618.20 rows=1,124 width=310) (actual time=6.258..8.068 rows=1,007 loops=1)

  • Hash Cond: (lf.load_id = l.id)
6. 0.555 0.555 ↓ 1.0 4,367 1

Seq Scan on load_fact lf (cost=0.00..303.63 rows=4,363 width=138) (actual time=0.025..0.555 rows=4,367 loops=1)

7. 0.629 6.207 ↑ 1.2 943 1

Hash (cost=273.22..273.22 rows=1,100 width=188) (actual time=6.207..6.207 rows=943 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 170kB
8. 0.597 5.578 ↑ 1.2 943 1

Hash Join (cost=226.10..273.22 rows=1,100 width=188) (actual time=4.817..5.578 rows=943 loops=1)

  • Hash Cond: (tl.id = l.id)
9. 0.205 0.205 ↑ 1.2 943 1

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

  • Filter: should_return
10. 2.448 4.776 ↑ 1.0 4,242 1

Hash (cost=172.71..172.71 rows=4,271 width=180) (actual time=4.776..4.776 rows=4,242 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 933kB
11. 2.328 2.328 ↑ 1.0 4,242 1

Seq Scan on load l (cost=0.00..172.71 rows=4,271 width=180) (actual time=0.009..2.328 rows=4,242 loops=1)

12.          

CTE paged_loads

13. 0.006 8.915 ↑ 1.0 30 1

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

14. 8.909 8.909 ↑ 12.5 30 1

CTE Scan on q q_1 (cost=0.00..25.29 rows=375 width=3,795) (actual time=8.852..8.909 rows=30 loops=1)

  • Filter: (row_number > 0)
15.          

CTE stops

16. 0.000 241.334 ↑ 6.9 29 1

HashAggregate (cost=15.85..19.35 rows=200 width=72) (actual time=241.185..241.334 rows=29 loops=1)

  • Group Key: s_1.parent_id
17.          

Initplan (forHashAggregate)

18. 8.923 8.923 ↑ 1.0 30 1

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

19. 240.778 240.778 ↑ 14.3 70 1

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

20. 241.820 241.820 ↑ 6.9 29 1

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

21. 0.011 0.016 ↑ 1.0 30 1

Hash (cost=0.60..0.60 rows=30 width=3,795) (actual time=0.016..0.016 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
22. 0.005 0.005 ↑ 1.0 30 1

CTE Scan on paged_loads q (cost=0.00..0.60 rows=30 width=3,795) (actual time=0.001..0.005 rows=30 loops=1)

Planning time : 0.874 ms
Execution time : 242.607 ms