explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HGm7

Settings
# exclusive inclusive rows x rows loops node
1. 7.931 147.731 ↓ 72.2 12,415 1

Nested Loop (cost=6,454.15..45,525.88 rows=172 width=8) (actual time=123.181..147.731 rows=12,415 loops=1)

2.          

CTE time_slices

3. 60.194 60.194 ↓ 2.6 2,624 1

Function Scan on d_get_features_valid_time_slices fts (cost=0.25..10.25 rows=1,000 width=8) (actual time=60.009..60.194 rows=2,624 loops=1)

4.          

CTE matched_data_field

5. 1.840 126.019 ↓ 1.7 11,765 1

Unique (cost=6,392.96..6,443.47 rows=6,735 width=36) (actual time=123.166..126.019 rows=11,765 loops=1)

6. 6.244 124.179 ↓ 1.9 12,835 1

Sort (cost=6,392.96..6,409.79 rows=6,735 width=36) (actual time=123.166..124.179 rows=12,835 loops=1)

  • Sort Key: ts.feature_id, df.name, ((ts.interpretation <> 'TEMPDELTA'::aixm_data.ts_interpretation)), ts.sequence DESC
  • Sort Method: quicksort Memory: 1387kB
7. 2.284 117.935 ↓ 1.9 12,835 1

Hash Join (cost=4,817.69..5,964.69 rows=6,735 width=36) (actual time=104.018..117.935 rows=12,835 loops=1)

  • Hash Cond: (v_1.data_field_id = df.id)
8. 1.978 115.211 ↓ 1.9 12,835 1

Nested Loop (cost=4,755.99..5,793.55 rows=6,735 width=24) (actual time=103.569..115.211 rows=12,835 loops=1)

9. 1.885 105.361 ↓ 2.6 2,624 1

Hash Join (cost=4,755.56..4,789.31 rows=1,000 width=20) (actual time=103.549..105.361 rows=2,624 loops=1)

  • Hash Cond: (fts_1.id = ts.id)
10. 60.548 60.548 ↓ 2.6 2,624 1

CTE Scan on time_slices fts_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=60.009..60.548 rows=2,624 loops=1)

11. 23.683 42.928 ↑ 1.0 140,425 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9179kB
12. 19.245 19.245 ↑ 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.010..19.245 rows=140,425 loops=1)

13. 7.872 7.872 ↑ 1.4 5 2,624

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=2,624)

  • Index Cond: (property_group_id = ts.property_group_id)
14. 0.218 0.440 ↑ 1.0 1,898 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 107kB
15. 0.222 0.222 ↑ 1.0 1,898 1

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

16. 128.035 128.035 ↓ 1.7 11,765 1

CTE Scan on matched_data_field mdf (cost=0.00..134.70 rows=6,735 width=12) (actual time=123.169..128.035 rows=11,765 loops=1)

17. 11.765 11.765 ↑ 1.0 1 11,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=11,765)

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