explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ikci

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

HashAggregate (cost=16,470.46..16,470.49 rows=3 width=8) (actual time=18,772.906..18,782.010 rows=135,569 loops=1)

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

CTE features_ts

3. 27.119 255.890 ↓ 3.5 139,954 1

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

4. 54.852 228.771 ↓ 3.5 139,956 1

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

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

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

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

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

  • Group Key: feature.id
7. 11.479 11.479 ↑ 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.479 rows=135,552 loops=1)

8. 28.314 62.536 ↓ 3.5 139,956 1

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

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

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

  • Hash Cond: (ts.project_id = p.id)
10. 9.275 9.275 ↑ 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.005..9.275 rows=140,425 loops=1)

11. 0.004 0.010 ↓ 2.0 4 1

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

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

Seq Scan on project p (cost=0.00..1.10 rows=2 width=4) (actual time=0.006..0.006 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. 308.539 308.539 ↓ 696.3 139,954 1

CTE Scan on features_ts fts_1 (cost=0.00..1,103.33 rows=201 width=20) (actual time=212.197..308.539 rows=139,954 loops=1)

  • Filter: (valid_time @> ('now'::cstring)::timestamp without time zone)
15.          

CTE last_baseline

16. 17.158 382.007 ↓ 134,331.0 134,331 1

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

17. 26.460 364.849 ↓ 138,713.0 138,713 1

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

  • Sort Key: vts.feature_id, vts.valid_time_begin DESC
  • Sort Method: quicksort Memory: 16981kB
18. 338.389 338.389 ↓ 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=212.199..338.389 rows=138,713 loops=1)

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

CTE non_overlapped_permdelta

20. 9,150.894 18,233.123 ↓ 1,238.0 1,238 1

Nested Loop Left Join (cost=0.00..4.56 rows=1 width=20) (actual time=0.083..18,233.123 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.519 10.519 ↓ 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.045..10.519 rows=1,241 loops=1)

  • Filter: (interpretation = 'PERMDELTA'::aixm_data.ts_interpretation)
  • Rows Removed by Filter: 138713
22. 9,071.710 9,071.710 ↓ 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.310 rows=134,331 loops=1,241)

23.          

CTE tempdeltas

24. 7.981 7.981 ↓ 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=7.981..7.981 rows=0 loops=1)

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

CTE final_time_slices

26. 75.784 18,727.781 ↓ 45,189.7 135,569 1

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

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

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

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

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

29. 18,235.467 18,235.467 ↓ 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.084..18,235.467 rows=1,238 loops=1)

30. 7.982 7.982 ↓ 0.0 0 1

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

31. 18,748.350 18,748.350 ↓ 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,696.838..18,748.350 rows=135,569 loops=1)