explain.depesz.com

PostgreSQL's explain analyze made readable

Result: naRg

Settings
# exclusive inclusive rows x rows loops node
1. 446.592 446.592 ↓ 3,081.1 135,569 1

CTE Scan on valid_time_slices vts (cost=14,966.53..14,967.41 rows=44 width=8) (actual time=394.553..446.592 rows=135,569 loops=1)

2.          

CTE valid_time_slices_by_time

3. 37.529 178.144 ↓ 3.5 139,956 1

Hash Join (cost=7,937.16..12,235.49 rows=40,121 width=20) (actual time=106.780..178.144 rows=139,956 loops=1)

  • Hash Cond: (feature.id = ts.feature_id)
4. 60.613 72.478 ↑ 1.0 135,552 1

HashAggregate (cost=3,155.40..4,510.92 rows=135,552 width=4) (actual time=38.352..72.478 rows=135,552 loops=1)

  • Group Key: feature.id
5. 11.865 11.865 ↑ 1.0 135,552 1

Seq Scan on feature (cost=0.00..2,816.52 rows=135,552 width=4) (actual time=0.006..11.865 rows=135,552 loops=1)

6. 25.510 68.137 ↓ 3.5 139,956 1

Hash (cost=4,280.25..4,280.25 rows=40,121 width=20) (actual time=68.137..68.137 rows=139,956 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 9148kB
7. 20.361 42.627 ↓ 3.5 139,956 1

Hash Join (cost=1.13..4,280.25 rows=40,121 width=20) (actual time=0.042..42.627 rows=139,956 loops=1)

  • Hash Cond: (ts.project_id = p.id)
8. 22.254 22.254 ↑ 1.0 140,397 1

Seq Scan on time_slice ts (cost=0.00..3,351.31 rows=140,425 width=24) (actual time=0.017..22.254 rows=140,397 loops=1)

  • Filter: (valid_time @> '2018-11-26 03:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 28
9. 0.004 0.012 ↓ 2.0 4 1

Hash (cost=1.10..1.10 rows=2 width=4) (actual time=0.012..0.012 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.008 0.008 ↓ 2.0 4 1

Seq Scan on project p (cost=0.00..1.10 rows=2 width=4) (actual time=0.007..0.008 rows=4 loops=1)

  • Filter: ((status = 'PUBLISHED'::wf.project_status) OR (id = 7))
  • Rows Removed by Filter: 3
11.          

CTE last_baseline

12. 17.584 278.649 ↓ 67,165.5 134,331 1

Unique (cost=910.41..911.42 rows=2 width=20) (actual time=244.331..278.649 rows=134,331 loops=1)

13. 50.032 261.065 ↓ 690.1 138,713 1

Sort (cost=910.41..910.91 rows=201 width=20) (actual time=244.329..261.065 rows=138,713 loops=1)

  • Sort Key: vts_1.feature_id, vts_1.sequence DESC, vts_1.correction DESC
  • Sort Method: quicksort Memory: 16958kB
14. 211.033 211.033 ↓ 690.1 138,713 1

CTE Scan on valid_time_slices_by_time vts_1 (cost=0.00..902.72 rows=201 width=20) (actual time=106.783..211.033 rows=138,713 loops=1)

  • Filter: (interpretation = 'BASELINE'::aixm_data.ts_interpretation)
  • Rows Removed by Filter: 1243
15.          

CTE non_overlapped_permdelta

16. 0.159 37.097 ↓ 59.0 1,238 1

Unique (cost=905.64..906.15 rows=21 width=16) (actual time=36.853..37.097 rows=1,238 loops=1)

17. 0.389 36.938 ↓ 18.2 1,239 1

Sort (cost=905.64..905.81 rows=68 width=16) (actual time=36.852..36.938 rows=1,239 loops=1)

  • Sort Key: vts_2.feature_id, vts_2.sequence DESC, vts_2.correction DESC, vts_2.id DESC
  • Sort Method: quicksort Memory: 107kB
18. 0.533 36.549 ↓ 18.2 1,239 1

Hash Left Join (cost=0.07..903.57 rows=68 width=16) (actual time=27.032..36.549 rows=1,239 loops=1)

  • Hash Cond: (vts_2.feature_id = lb.feature_id)
  • Filter: ((lb.sequence < vts_2.sequence) OR (lb.id IS NULL))
  • Rows Removed by Filter: 4
19. 9.010 9.010 ↓ 6.2 1,243 1

CTE Scan on valid_time_slices_by_time vts_2 (cost=0.00..902.72 rows=201 width=16) (actual time=0.015..9.010 rows=1,243 loops=1)

  • Filter: (interpretation = 'PERMDELTA'::aixm_data.ts_interpretation)
  • Rows Removed by Filter: 138713
20. 15.074 27.006 ↓ 67,165.5 134,331 1

Hash (cost=0.04..0.04 rows=2 width=12) (actual time=27.006..27.006 rows=134,331 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7821kB
21. 11.932 11.932 ↓ 67,165.5 134,331 1

CTE Scan on last_baseline lb (cost=0.00..0.04 rows=2 width=12) (actual time=0.000..11.932 rows=134,331 loops=1)

22.          

CTE valid_time_slices

23. 73.531 426.244 ↓ 3,081.1 135,569 1

HashAggregate (cost=913.04..913.48 rows=44 width=12) (actual time=394.552..426.244 rows=135,569 loops=1)

  • Group Key: lb_1.id, lb_1.feature_id
24. 6.939 352.713 ↓ 3,081.1 135,569 1

Append (cost=0.00..912.82 rows=44 width=12) (actual time=244.332..352.713 rows=135,569 loops=1)

25. 300.166 300.166 ↓ 67,165.5 134,331 1

CTE Scan on last_baseline lb_1 (cost=0.00..0.04 rows=2 width=8) (actual time=244.332..300.166 rows=134,331 loops=1)

26. 37.279 37.279 ↓ 59.0 1,238 1

CTE Scan on non_overlapped_permdelta pd (cost=0.00..0.42 rows=21 width=8) (actual time=36.855..37.279 rows=1,238 loops=1)

27. 0.001 8.329 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=910.41..912.13 rows=21 width=16) (actual time=8.329..8.329 rows=0 loops=1)

28. 0.000 8.328 ↓ 0.0 0 1

Unique (cost=910.41..911.92 rows=21 width=16) (actual time=8.328..8.328 rows=0 loops=1)

29. 0.010 8.328 ↓ 0.0 0 1

Sort (cost=910.41..910.91 rows=201 width=16) (actual time=8.328..8.328 rows=0 loops=1)

  • Sort Key: td.feature_id, td.sequence DESC, td.correction DESC, td.id DESC
  • Sort Method: quicksort Memory: 25kB
30. 8.318 8.318 ↓ 0.0 0 1

CTE Scan on valid_time_slices_by_time td (cost=0.00..902.72 rows=201 width=16) (actual time=8.318..8.318 rows=0 loops=1)

  • Filter: (interpretation = 'TEMPDELTA'::aixm_data.ts_interpretation)
  • Rows Removed by Filter: 139956