explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dxKD

Settings
# exclusive inclusive rows x rows loops node
1. 42.719 359.246 ↓ 417.2 71,765 1

Nested Loop (cost=9,616.46..48,688.20 rows=172 width=8) (actual time=216.838..359.246 rows=71,765 loops=1)

2.          

CTE time_slices

3. 62.757 73.272 ↓ 14.4 14,353 1

Function Scan on d_get_features_valid_time_slices_new_new fts (cost=3,162.57..3,172.57 rows=1,000 width=24) (actual time=71.969..73.272 rows=14,353 loops=1)

4.          

Initplan (forFunction Scan)

5. 0.896 10.515 ↑ 1.0 1 1

Aggregate (cost=3,162.31..3,162.32 rows=1 width=4) (actual time=10.515..10.515 rows=1 loops=1)

6.          

Initplan (forAggregate)

7. 0.050 0.050 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)

8. 9.569 9.569 ↓ 5.4 14,364 1

Seq Scan on feature (cost=0.00..3,155.40 rows=2,658 width=4) (actual time=0.763..9.569 rows=14,364 loops=1)

  • Filter: (data_class_id = $0)
  • Rows Removed by Filter: 121188
9.          

CTE matched_data_field

10. 10.460 234.041 ↓ 10.7 71,765 1

Unique (cost=6,392.96..6,443.47 rows=6,735 width=36) (actual time=216.815..234.041 rows=71,765 loops=1)

11. 35.570 223.581 ↓ 10.7 71,765 1

Sort (cost=6,392.96..6,409.79 rows=6,735 width=36) (actual time=216.814..223.581 rows=71,765 loops=1)

  • Sort Key: ts.feature_id, df.name, ((ts.interpretation <> 'TEMPDELTA'::aixm_data.ts_interpretation)), ts.sequence DESC
  • Sort Method: quicksort Memory: 8679kB
12. 12.467 188.011 ↓ 10.7 71,765 1

Hash Join (cost=4,817.69..5,964.69 rows=6,735 width=36) (actual time=113.691..188.011 rows=71,765 loops=1)

  • Hash Cond: (v_1.data_field_id = df.id)
13. 7.905 175.174 ↓ 10.7 71,765 1

Nested Loop (cost=4,755.99..5,793.55 rows=6,735 width=24) (actual time=113.315..175.174 rows=71,765 loops=1)

14. 7.198 124.210 ↓ 14.4 14,353 1

Hash Join (cost=4,755.56..4,789.31 rows=1,000 width=20) (actual time=113.298..124.210 rows=14,353 loops=1)

  • Hash Cond: (fts_1.id = ts.id)
15. 75.933 75.933 ↓ 14.4 14,353 1

CTE Scan on time_slices fts_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=71.970..75.933 rows=14,353 loops=1)

16. 23.354 41.079 ↑ 1.0 140,425 1

Hash (cost=3,000.25..3,000.25 rows=140,425 width=20) (actual time=41.079..41.079 rows=140,425 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9179kB
17. 17.725 17.725 ↑ 1.0 140,425 1

Seq Scan on time_slice ts (cost=0.00..3,000.25 rows=140,425 width=20) (actual time=0.008..17.725 rows=140,425 loops=1)

18. 43.059 43.059 ↑ 1.4 5 14,353

Index Scan using value_property_group_id_idx on value v_1 (cost=0.43..0.93 rows=7 width=8) (actual time=0.002..0.003 rows=5 loops=14,353)

  • Index Cond: (property_group_id = ts.property_group_id)
19. 0.196 0.370 ↑ 1.0 1,898 1

Hash (cost=37.98..37.98 rows=1,898 width=16) (actual time=0.370..0.370 rows=1,898 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 107kB
20. 0.174 0.174 ↑ 1.0 1,898 1

Seq Scan on data_field df (cost=0.00..37.98 rows=1,898 width=16) (actual time=0.003..0.174 rows=1,898 loops=1)

21. 244.762 244.762 ↓ 10.7 71,765 1

CTE Scan on matched_data_field mdf (cost=0.00..134.70 rows=6,735 width=12) (actual time=216.817..244.762 rows=71,765 loops=1)

22. 71.765 71.765 ↑ 1.0 1 71,765

Index Scan using value_property_group_id_idx on value v (cost=0.43..5.77 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=71,765)

  • Index Cond: (property_group_id = mdf.property_group_id)
  • Filter: (mdf.data_field_id = data_field_id)
  • Rows Removed by Filter: 4