explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RvSl

Settings
# exclusive inclusive rows x rows loops node
1. 91.923 592.451 ↓ 1.0 12,415 1

Merge Join (cost=148,730.96..260,550.65 rows=12,064 width=8) (actual time=366.621..592.451 rows=12,415 loops=1)

  • Merge Cond: (v.property_group_id = mdf.property_group_id)
  • Join Filter: (mdf.data_field_id = v.data_field_id)
  • Rows Removed by Join Filter: 47449
2.          

CTE time_slices

3. 104.413 104.413 ↓ 2.6 2,624 1

Function Scan on d_get_features_valid_time_slices fts (cost=0.25..10.25 rows=1,000 width=4) (actual time=104.267..104.413 rows=2,624 loops=1)

4.          

CTE matched_data_field

5. 1.688 317.607 ↑ 40.2 11,765 1

Unique (cost=91,143.87..94,690.50 rows=472,885 width=36) (actual time=315.346..317.607 rows=11,765 loops=1)

6. 4.942 315.919 ↑ 36.8 12,835 1

Sort (cost=91,143.87..92,326.08 rows=472,885 width=36) (actual time=315.344..315.919 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. 1.990 310.977 ↑ 36.8 12,835 1

Hash Join (cost=2,449.36..46,571.78 rows=472,885 width=36) (actual time=142.995..310.977 rows=12,835 loops=1)

  • Hash Cond: (v_1.data_field_id = df.id)
8. 113.598 308.602 ↑ 36.8 12,835 1

Hash Join (cost=2,387.65..38,825.70 rows=472,885 width=24) (actual time=142.600..308.602 rows=12,835 loops=1)

  • Hash Cond: (v_1.property_group_id = ts.property_group_id)
9. 85.861 85.861 ↑ 1.0 1,655,362 1

Seq Scan on value v_1 (cost=0.00..25,501.62 rows=1,655,362 width=8) (actual time=0.002..85.861 rows=1,655,362 loops=1)

10. 0.533 109.143 ↑ 26.8 2,624 1

Hash (cost=1,510.00..1,510.00 rows=70,212 width=20) (actual time=109.143..109.143 rows=2,624 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1158kB
11. 0.641 108.610 ↑ 26.8 2,624 1

Nested Loop (cost=22.92..1,510.00 rows=70,212 width=20) (actual time=105.118..108.610 rows=2,624 loops=1)

12. 0.600 105.345 ↓ 13.1 2,624 1

HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=105.100..105.345 rows=2,624 loops=1)

  • Group Key: time_slices.id
13. 104.745 104.745 ↓ 2.6 2,624 1

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

14. 2.624 2.624 ↑ 1.0 1 2,624

Index Scan using time_slice_pkey on time_slice ts (cost=0.42..7.42 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=2,624)

  • Index Cond: (id = time_slices.id)
15. 0.216 0.385 ↑ 1.0 1,898 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 107kB
16. 0.169 0.169 ↑ 1.0 1,898 1

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

17. 177.110 177.110 ↑ 1.0 1,655,333 1

Index Scan using value_property_group_id_idx on value v (cost=0.43..51,945.86 rows=1,655,362 width=12) (actual time=0.004..177.110 rows=1,655,333 loops=1)

18. 3.954 323.418 ↑ 7.9 59,854 1

Sort (cost=54,029.78..55,212.00 rows=472,885 width=12) (actual time=321.304..323.418 rows=59,854 loops=1)

  • Sort Key: mdf.property_group_id
  • Sort Method: quicksort Memory: 936kB
19. 319.464 319.464 ↑ 40.2 11,765 1

CTE Scan on matched_data_field mdf (cost=0.00..9,457.70 rows=472,885 width=12) (actual time=315.348..319.464 rows=11,765 loops=1)