explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MqwQ

Settings
# exclusive inclusive rows x rows loops node
1. 36.325 559.253 ↓ 44,529.0 133,587 1

HashAggregate (cost=16,470.46..16,470.49 rows=3 width=8) (actual time=550.015..559.253 rows=133,587 loops=1)

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

CTE features_ts

3. 27.039 256.349 ↓ 3.4 137,969 1

Unique (cost=14,952.09..15,353.30 rows=40,121 width=42) (actual time=212.554..256.349 rows=137,969 loops=1)

4. 57.216 229.310 ↓ 3.4 137,969 1

Sort (cost=14,952.09..15,052.39 rows=40,121 width=42) (actual time=212.552..229.310 rows=137,969 loops=1)

  • Sort Key: ts.feature_id, ts.interpretation, ts.sequence, ts.correction DESC
  • Sort Method: quicksort Memory: 16923kB
5. 39.981 172.094 ↓ 3.4 137,969 1

Hash Join (cost=7,586.10..11,884.43 rows=40,121 width=42) (actual time=100.024..172.094 rows=137,969 loops=1)

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

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

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

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

8. 27.954 61.805 ↓ 3.4 137,969 1

Hash (cost=3,929.18..3,929.18 rows=40,121 width=42) (actual time=61.805..61.805 rows=137,969 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12827kB
9. 24.722 33.851 ↓ 3.4 137,969 1

Hash Join (cost=1.13..3,929.18 rows=40,121 width=42) (actual time=0.026..33.851 rows=137,969 loops=1)

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

11. 0.001 0.009 ↓ 1.5 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.008 0.008 ↓ 1.5 3 1

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

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

CTE valid_time_slices_by_time

14. 308.682 308.682 ↓ 686.4 137,969 1

CTE Scan on features_ts fts_1 (cost=0.00..1,103.33 rows=201 width=20) (actual time=212.575..308.682 rows=137,969 loops=1)

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

CTE last_baseline

16. 17.098 382.101 ↓ 133,587.0 133,587 1

Unique (cost=4.53..4.54 rows=1 width=20) (actual time=355.641..382.101 rows=133,587 loops=1)

17. 26.496 365.003 ↓ 137,969.0 137,969 1

Sort (cost=4.53..4.54 rows=1 width=20) (actual time=355.640..365.003 rows=137,969 loops=1)

  • Sort Key: vts.feature_id, vts.valid_time_begin DESC
  • Sort Method: quicksort Memory: 16923kB
18. 338.507 338.507 ↓ 137,969.0 137,969 1

CTE Scan on valid_time_slices_by_time vts (cost=0.00..4.52 rows=1 width=20) (actual time=212.578..338.507 rows=137,969 loops=1)

  • Filter: (interpretation = 'BASELINE'::aixm_data.ts_interpretation)
19.          

CTE non_overlapped_permdelta

20. 0.000 7.917 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..4.56 rows=1 width=20) (actual time=7.917..7.917 rows=0 loops=1)

  • Join Filter: (vts_1.feature_id = lb.feature_id)
  • Filter: ((lb.valid_time_begin <= vts_1.valid_time_begin) OR (lb.id IS NULL))
21. 7.917 7.917 ↓ 0.0 0 1

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

  • Filter: (interpretation = 'PERMDELTA'::aixm_data.ts_interpretation)
  • Rows Removed by Filter: 137969
22. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_baseline lb (cost=0.00..0.02 rows=1 width=16) (never executed)

23.          

CTE tempdeltas

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

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

CTE final_time_slices

26. 74.499 502.211 ↓ 44,529.0 133,587 1

HashAggregate (cost=0.10..0.14 rows=3 width=8) (actual time=467.507..502.211 rows=133,587 loops=1)

  • Group Key: lb_1.id, lb_1.feature_id
27. 6.883 427.712 ↓ 44,529.0 133,587 1

Append (cost=0.00..0.09 rows=3 width=8) (actual time=355.643..427.712 rows=133,587 loops=1)

28. 402.613 402.613 ↓ 133,587.0 133,587 1

CTE Scan on last_baseline lb_1 (cost=0.00..0.02 rows=1 width=8) (actual time=355.643..402.613 rows=133,587 loops=1)

29. 7.919 7.919 ↓ 0.0 0 1

CTE Scan on non_overlapped_permdelta pd (cost=0.00..0.02 rows=1 width=8) (actual time=7.919..7.919 rows=0 loops=1)

30. 10.297 10.297 ↓ 0.0 0 1

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

31. 522.928 522.928 ↓ 44,529.0 133,587 1

CTE Scan on final_time_slices fts (cost=0.00..0.06 rows=3 width=8) (actual time=467.509..522.928 rows=133,587 loops=1)