explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GEF6

Settings
# exclusive inclusive rows x rows loops node
1. 7.717 144.853 ↓ 72.2 12,415 1

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

2.          

CTE time_slices

3. 62.971 62.971 ↓ 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=62.767..62.971 rows=2,624 loops=1)

4.          

CTE matched_data_field

5. 1.803 123.432 ↓ 1.7 11,765 1

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

6. 5.863 121.629 ↓ 1.9 12,835 1

Sort (cost=6,392.96..6,409.79 rows=6,735 width=36) (actual time=120.708..121.629 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.254 115.766 ↓ 1.9 12,835 1

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

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

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

9. 1.275 105.247 ↓ 2.6 2,624 1

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

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

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

11. 23.375 40.673 ↑ 1.0 140,425 1

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

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

13. 5.248 5.248 ↑ 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.002 rows=5 loops=2,624)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 107kB
15. 0.331 0.331 ↑ 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.331 rows=1,898 loops=1)

16. 125.371 125.371 ↓ 1.7 11,765 1

CTE Scan on matched_data_field mdf (cost=0.00..134.70 rows=6,735 width=12) (actual time=120.711..125.371 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