explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iqjBl

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=144,751.72..144,751.72 rows=1 width=263) (actual rows= loops=)

  • Sort Key: cr.mobile_id, ((cr.time_from + ('-3 hours'::cstring)::interval))
2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=144,725.65..144,751.71 rows=1 width=263) (actual rows= loops=)

  • Group Key: cr.entity_name, cr.mobile_id, cr.mobile_name, cr.tasks_id, cr.group_type_name, cr.group_name, cr.type_id, cr.type_name, cr.code_id, cr.code, cr.data_code, cr.time_from, cr.time_to, cr.duration, cr.data1, cr.data2, cr.data3, cr.next_code, cr.next_data_code
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,452.88..142,632.73 rows=34,882 width=196) (actual rows= loops=)

  • Hash Cond: (dr.status_id = s.id)
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=9,418.31..142,506.21 rows=34,882 width=199) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Subquery Scan on cr (cost=8,144.99..8,147.29 rows=1 width=187) (actual rows= loops=)

  • Filter: (((cr.time_from + ('-3 hours'::cstring)::interval) >= '2020-09-14 00:00:00'::timestamp without time zone) AND ((cr.time_from + ('-3 hours'::cstring)::interval) <= '2020-10-14 00:00:00'::timestamp without time zone))
6. 0.000 0.000 ↓ 0.0

WindowAgg (cost=8,144.99..8,146.27 rows=34 width=195) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=8,144.99..8,145.08 rows=34 width=119) (actual rows= loops=)

  • Sort Key: cr_1.mobile_id, rc.type_id, cr_1.gps_time DESC
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=11.04..8,144.13 rows=34 width=119) (actual rows= loops=)

  • Join Filter: (rt.id = rc.type_id)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=11.04..8,137.16 rows=34 width=106) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10.76..8,123.18 rows=40 width=66) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10.34..135.46 rows=27 width=50) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10.19..130.83 rows=27 width=44) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10.05..126.27 rows=27 width=36) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..8.93 rows=1 width=19) (actual rows= loops=)

  • Join Filter: (e.id = users_by_entities.entity_id)
15. 0.000 0.000 ↓ 0.0

Index Only Scan using users_by_entities_pkey on users_by_entities (cost=0.28..4.29 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 420)
16. 0.000 0.000 ↓ 0.0

Seq Scan on entities e (cost=0.00..3.17 rows=117 width=15) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on mobiles m (cost=9.77..117.00 rows=35 width=29) (actual rows= loops=)

  • Recheck Cond: (entity_id = e.id)
18. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on entity_abbreviation (cost=0.00..9.76 rows=35 width=0) (actual rows= loops=)

  • Index Cond: (entity_id = e.id)
19. 0.000 0.000 ↓ 0.0

Index Scan using mobile_types_group_pkey on mobile_types_group mtg (cost=0.14..0.17 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (id = m.mobile_types_group_id)
20. 0.000 0.000 ↓ 0.0

Index Scan using mobile_group_pkey on mobile_group mg (cost=0.15..0.17 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (id = m.mobile_group_id)
21. 0.000 0.000 ↓ 0.0

Index Scan using rfid_code_records_mobile_id_idx on code_records cr_1 (cost=0.42..295.47 rows=37 width=16) (actual rows= loops=)

  • Index Cond: (mobile_id = m.id)
  • Filter: (((gps_time + ('-3 hours'::cstring)::interval) >= '2020-09-14 00:00:00'::timestamp without time zone) AND ((gps_time + ('-3 hours'::cstring)::interval) <= '2020-10-14 00:00:00'::timestamp without time zone))
22. 0.000 0.000 ↓ 0.0

Index Scan using rfid_codes_id_pk on codes rc (cost=0.28..0.35 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (id = cr_1.code_id)
  • Filter: (type_id IS NOT NULL)
23. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.18 rows=12 width=13) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on types rt (cost=0.00..1.12 rows=12 width=13) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on data_records dr (cost=1,273.32..134,010.10 rows=34,882 width=24) (actual rows= loops=)

  • Recheck Cond: ((mobile_id = cr.mobile_id) AND (gps_time >= cr.time_from) AND (gps_time < cr.time_to))
26. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on data_records2_mobile_time (cost=0.00..1,264.60 rows=34,882 width=0) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr.mobile_id) AND (gps_time >= cr.time_from) AND (gps_time < cr.time_to))
27. 0.000 0.000 ↓ 0.0

Hash (cost=22.03..22.03 rows=1,003 width=5) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on statuses s (cost=0.00..22.03 rows=1,003 width=5) (actual rows= loops=)

29.          

SubPlan (for HashAggregate)

30. 0.000 0.000 ↓ 0.0

Limit (cost=0.57..8.59 rows=1 width=32) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Index Scan using data_records2_mobile_time on data_records dr2 (cost=0.57..8.59 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr.mobile_id) AND (gps_time = cr.time_from))
32. 0.000 0.000 ↓ 0.0

Limit (cost=0.57..8.59 rows=1 width=32) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Scan using data_records2_mobile_time on data_records dr2_1 (cost=0.57..8.59 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr.mobile_id) AND (gps_time = cr.time_from))
34. 0.000 0.000 ↓ 0.0

Limit (cost=0.57..8.59 rows=1 width=32) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Scan using data_records2_mobile_time on data_records dr2_2 (cost=0.57..8.59 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr.mobile_id) AND (gps_time = cr.time_from))