explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VwJY

Settings
# exclusive inclusive rows x rows loops node
1. 46.689 18,906.082 ↓ 45,189.7 135,569 1

HashAggregate (cost=16,269.85..16,269.88 rows=3 width=8) (actual time=18,896.879..18,906.082 rows=135,569 loops=1)

  • Group Key: fts.id, fts.feature_id
2.          

CTE features_ts

3. 26.704 285.652 ↓ 3.5 139,954 1

Unique (cost=14,952.09..15,353.30 rows=40,121 width=42) (actual time=242.976..285.652 rows=139,954 loops=1)

4. 53.399 258.948 ↓ 3.5 139,956 1

Sort (cost=14,952.09..15,052.39 rows=40,121 width=42) (actual time=242.975..258.948 rows=139,956 loops=1)

  • Sort Key: ts.feature_id, ts.interpretation, ts.sequence, ts.correction DESC
  • Sort Method: quicksort Memory: 17079kB
5. 39.273 205.549 ↓ 3.5 139,956 1

Hash Join (cost=7,586.10..11,884.43 rows=40,121 width=42) (actual time=135.004..205.549 rows=139,956 loops=1)

  • Hash Cond: (feature.id = ts.feature_id)
6. 70.514 94.045 ↑ 1.0 135,552 1

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

  • Group Key: feature.id
7. 23.531 23.531 ↑ 1.0 135,552 1

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

8. 30.144 72.231 ↓ 3.5 139,956 1

Hash (cost=3,929.18..3,929.18 rows=40,121 width=42) (actual time=72.231..72.231 rows=139,956 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12975kB
9. 28.751 42.087 ↓ 3.5 139,956 1

Hash Join (cost=1.13..3,929.18 rows=40,121 width=42) (actual time=0.057..42.087 rows=139,956 loops=1)

  • Hash Cond: (ts.project_id = p.id)
10. 13.307 13.307 ↑ 1.0 140,425 1

Seq Scan on time_slice ts (cost=0.00..3,000.25 rows=140,425 width=46) (actual time=0.014..13.307 rows=140,425 loops=1)

11. 0.008 0.029 ↓ 2.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.021 0.021 ↓ 2.0 4 1

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

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

CTE valid_time_slices_by_time

14. 313.449 313.449 ↓ 696.3 139,954 1

CTE Scan on features_ts fts_1 (cost=0.00..902.72 rows=201 width=20) (actual time=242.985..313.449 rows=139,954 loops=1)

  • Filter: (valid_time @> '2018-11-26 03:00:00'::timestamp without time zone)
15.          

CTE last_baseline

16. 18.372 385.472 ↓ 134,331.0 134,331 1

Unique (cost=4.53..4.54 rows=1 width=20) (actual time=357.605..385.472 rows=134,331 loops=1)

17. 24.411 367.100 ↓ 138,713.0 138,713 1

Sort (cost=4.53..4.54 rows=1 width=20) (actual time=357.604..367.100 rows=138,713 loops=1)

  • Sort Key: vts.feature_id, vts.valid_time_begin DESC
  • Sort Method: quicksort Memory: 16981kB
18. 342.689 342.689 ↓ 138,713.0 138,713 1

CTE Scan on valid_time_slices_by_time vts (cost=0.00..4.52 rows=1 width=20) (actual time=242.988..342.689 rows=138,713 loops=1)

  • Filter: (interpretation = 'BASELINE'::aixm_data.ts_interpretation)
  • Rows Removed by Filter: 1241
19.          

CTE non_overlapped_permdelta

20. 9,153.429 18,304.890 ↓ 1,238.0 1,238 1

Nested Loop Left Join (cost=0.00..4.56 rows=1 width=20) (actual time=0.079..18,304.890 rows=1,238 loops=1)

  • Join Filter: (vts_1.feature_id = lb.feature_id)
  • Rows Removed by Join Filter: 166703996
  • Filter: ((lb.valid_time_begin <= vts_1.valid_time_begin) OR (lb.id IS NULL))
  • Rows Removed by Filter: 3
21. 10.255 10.255 ↓ 1,241.0 1,241 1

CTE Scan on valid_time_slices_by_time vts_1 (cost=0.00..4.52 rows=1 width=20) (actual time=0.043..10.255 rows=1,241 loops=1)

  • Filter: (interpretation = 'PERMDELTA'::aixm_data.ts_interpretation)
  • Rows Removed by Filter: 138713
22. 9,141.206 9,141.206 ↓ 134,331.0 134,331 1,241

CTE Scan on last_baseline lb (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..7.366 rows=134,331 loops=1,241)

23.          

CTE tempdeltas

24. 8.350 8.350 ↓ 0.0 0 1

CTE Scan on valid_time_slices_by_time vts_2 (cost=0.00..4.52 rows=1 width=20) (actual time=8.350..8.350 rows=0 loops=1)

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

CTE final_time_slices

26. 80.320 18,823.197 ↓ 45,189.7 135,569 1

HashAggregate (cost=0.10..0.14 rows=3 width=8) (actual time=18,789.846..18,823.197 rows=135,569 loops=1)

  • Group Key: lb_1.id, lb_1.feature_id
27. 7.824 18,742.877 ↓ 45,189.7 135,569 1

Append (cost=0.00..0.09 rows=3 width=8) (actual time=357.606..18,742.877 rows=135,569 loops=1)

28. 419.732 419.732 ↓ 134,331.0 134,331 1

CTE Scan on last_baseline lb_1 (cost=0.00..0.02 rows=1 width=8) (actual time=357.606..419.732 rows=134,331 loops=1)

29. 18,306.971 18,306.971 ↓ 1,238.0 1,238 1

CTE Scan on non_overlapped_permdelta pd (cost=0.00..0.02 rows=1 width=8) (actual time=0.080..18,306.971 rows=1,238 loops=1)

30. 8.350 8.350 ↓ 0.0 0 1

CTE Scan on tempdeltas td (cost=0.00..0.02 rows=1 width=8) (actual time=8.350..8.350 rows=0 loops=1)

31. 18,859.393 18,859.393 ↓ 45,189.7 135,569 1

CTE Scan on final_time_slices fts (cost=0.00..0.06 rows=3 width=8) (actual time=18,789.848..18,859.393 rows=135,569 loops=1)