explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OIxb

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 89.015 ↓ 0.0 0 1

Limit (cost=5,747,128.40..5,747,128.52 rows=50 width=192) (actual time=89.015..89.015 rows=0 loops=1)

2. 0.004 89.013 ↓ 0.0 0 1

Sort (cost=5,747,128.40..6,058,001.49 rows=124,349,238 width=192) (actual time=89.013..89.013 rows=0 loops=1)

  • Sort Key: remote_sensor.created_at DESC
  • Sort Method: quicksort Memory: 25kB
3. 12.233 89.009 ↓ 0.0 0 1

Nested Loop (cost=24.78..1,616,336.13 rows=124,349,238 width=192) (actual time=89.008..89.009 rows=0 loops=1)

4. 6.580 6.580 ↑ 1.0 17,549 1

Seq Scan on identity_structure_permission (cost=0.00..337.06 rows=17,706 width=0) (actual time=0.006..6.580 rows=17,549 loops=1)

5. 14.216 70.196 ↓ 0.0 0 17,549

Materialize (cost=24.78..61,651.15 rows=7,023 width=192) (actual time=0.004..0.004 rows=0 loops=17,549)

6. 0.001 55.980 ↓ 0.0 0 1

Nested Loop Left Join (cost=24.78..61,616.03 rows=7,023 width=192) (actual time=55.979..55.980 rows=0 loops=1)

7. 0.003 55.979 ↓ 0.0 0 1

Append (cost=24.21..1,446.02 rows=7,023 width=128) (actual time=55.978..55.979 rows=0 loops=1)

8. 0.001 0.028 ↓ 0.0 0 1

Merge Join (cost=24.21..28.72 rows=1 width=128) (actual time=0.028..0.028 rows=0 loops=1)

  • Merge Cond: (remote_sensor.hvac_unit_id = hvac_unit.id)
9. 0.027 0.027 ↑ 9,407.0 1 1

Index Scan using remote_sensor_hvac_unit_id_idx on remote_sensor (cost=0.41..42,327.20 rows=9,407 width=128) (actual time=0.026..0.027 rows=1 loops=1)

  • Filter: ((tombstone IS FALSE) AND ((is_tstat IS FALSE) OR (is_tstat IS NULL)))
  • Rows Removed by Filter: 4
10. 0.000 0.000 ↓ 0.0 0

Sort (cost=23.79..23.79 rows=1 width=16) (never executed)

  • Sort Key: hvac_unit.id
11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.39..23.78 rows=1 width=16) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..22.71 rows=1 width=16) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.70..22.00 rows=2 width=8) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using isp_identity_id_idex on identity_structure_permission identity_structure_permission_1 (cost=0.29..9.14 rows=2 width=4) (never executed)

  • Index Cond: (identity_id = 224)
  • Filter: (is_installer IS NOT TRUE)
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using structure_pkey on structure (cost=0.41..6.43 rows=1 width=4) (never executed)

  • Index Cond: (id = identity_structure_permission_1.structure_id)
  • Heap Fetches: 0
16. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_integration_structure_structure_id on integration_structure (cost=0.28..0.33 rows=2 width=20) (never executed)

  • Index Cond: (structure_id = structure.id)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using hvac_unit_integration_structure on hvac_unit (cost=0.41..1.06 rows=1 width=32) (never executed)

  • Index Cond: (integration_structure_id = integration_structure.id)
18. 4.335 55.948 ↓ 0.0 0 1

Merge Join (cost=1,023.54..1,311.96 rows=7,022 width=128) (actual time=55.948..55.948 rows=0 loops=1)

  • Merge Cond: (hvac_unit_1.integration_structure_id = integration_structure_1.id)
19. 10.589 51.572 ↑ 8,185.5 11,613 1

Nested Loop (cost=1,000.82..1,220,190.41 rows=95,057,735 width=144) (actual time=0.700..51.572 rows=11,613 loops=1)

20. 0.027 0.027 ↑ 3,368.3 3 1

Index Only Scan using hvac_unit_integration_structure on hvac_unit hvac_unit_1 (cost=0.41..2,135.99 rows=10,105 width=16) (actual time=0.019..0.027 rows=3 loops=1)

  • Heap Fetches: 1
21. 10.715 40.956 ↑ 2.4 3,871 3

Materialize (cost=1,000.41..29,856.25 rows=9,407 width=128) (actual time=0.227..13.652 rows=3,871 loops=3)

22. 7.383 30.241 ↑ 1.6 5,806 1

Gather (cost=1,000.41..29,809.22 rows=9,407 width=128) (actual time=0.676..30.241 rows=5,806 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
23. 5.446 22.858 ↑ 2.0 1,935 3 / 3

Nested Loop (cost=0.41..27,868.52 rows=3,920 width=128) (actual time=0.031..22.858 rows=1,935 loops=3)

24. 11.606 11.606 ↑ 2.0 1,935 3 / 3

Parallel Seq Scan on remote_sensor remote_sensor_1 (cost=0.00..23,617.44 rows=3,920 width=128) (actual time=0.009..11.606 rows=1,935 loops=3)

  • Filter: ((tombstone IS FALSE) AND ((is_tstat IS FALSE) OR (is_tstat IS NULL)))
  • Rows Removed by Filter: 2,807
25. 5.806 5.806 ↑ 1.0 1 5,806 / 3

Index Only Scan using uniq_thermostat_id on thermostat (cost=0.41..1.08 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=5,806)

  • Index Cond: (id = remote_sensor_1.thermostat_id)
  • Heap Fetches: 864
26. 0.002 0.041 ↑ 1.0 1 1

Materialize (cost=22.72..22.72 rows=1 width=16) (actual time=0.040..0.041 rows=1 loops=1)

27. 0.006 0.039 ↑ 1.0 1 1

Sort (cost=22.72..22.72 rows=1 width=16) (actual time=0.038..0.039 rows=1 loops=1)

  • Sort Key: integration_structure_1.id
  • Sort Method: quicksort Memory: 25kB
28. 0.004 0.033 ↑ 1.0 1 1

Nested Loop (cost=0.98..22.71 rows=1 width=16) (actual time=0.029..0.033 rows=1 loops=1)

29. 0.002 0.021 ↑ 2.0 1 1

Nested Loop (cost=0.70..22.00 rows=2 width=8) (actual time=0.020..0.021 rows=1 loops=1)

30. 0.010 0.010 ↑ 2.0 1 1

Index Scan using isp_identity_id_idex on identity_structure_permission identity_structure_permission_2 (cost=0.29..9.14 rows=2 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (identity_id = 224)
  • Filter: (is_installer IS NOT TRUE)
31. 0.009 0.009 ↑ 1.0 1 1

Index Only Scan using structure_pkey on structure structure_1 (cost=0.41..6.43 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (id = identity_structure_permission_2.structure_id)
  • Heap Fetches: 0
32. 0.008 0.008 ↑ 2.0 1 1

Index Scan using ix_integration_structure_structure_id on integration_structure integration_structure_1 (cost=0.28..0.33 rows=2 width=20) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (structure_id = structure_1.id)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using remote_sensor_reading_uniq on remote_sensor_reading remote_sensor_reading_1 (cost=0.57..8.57 rows=1 width=64) (never executed)

  • Index Cond: (remote_sensor.current_reading_id = id)