explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rzjz

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 0.296 ↓ 0.0 0 1

Sort (cost=261.19..261.20 rows=2 width=120) (actual time=0.296..0.296 rows=0 loops=1)

  • Sort Key: n1.id, m1.dev_eui, m1.sent_date DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.000 0.277 ↓ 0.0 0 1

Append (cost=45.67..261.18 rows=2 width=120) (actual time=0.277..0.277 rows=0 loops=1)

3. 0.001 0.061 ↓ 0.0 0 1

Unique (cost=45.67..45.68 rows=1 width=108) (actual time=0.061..0.061 rows=0 loops=1)

4. 0.016 0.060 ↓ 0.0 0 1

Sort (cost=45.67..45.67 rows=1 width=108) (actual time=0.060..0.060 rows=0 loops=1)

  • Sort Key: n1.id, m1.dev_eui, m1.sent_date DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.001 0.044 ↓ 0.0 0 1

Nested Loop (cost=25.95..45.66 rows=1 width=108) (actual time=0.044..0.044 rows=0 loops=1)

  • Join Filter: ((m1.sent_date = m2.mindate) OR (m1.sent_date = m2.maxdate))
6. 0.000 0.043 ↓ 0.0 0 1

Nested Loop (cost=25.95..37.02 rows=1 width=125) (actual time=0.043..0.043 rows=0 loops=1)

7. 0.017 0.043 ↓ 0.0 0 1

Hash Join (cost=25.54..28.99 rows=1 width=125) (actual time=0.043..0.043 rows=0 loops=1)

  • Hash Cond: (p1.id = mi1.point_id)
8. 0.013 0.013 ↑ 105.0 1 1

Seq Scan on amr_point p1 (cost=0.00..3.05 rows=105 width=8) (actual time=0.013..0.013 rows=1 loops=1)

9. 0.000 0.013 ↓ 0.0 0 1

Hash (cost=25.52..25.52 rows=1 width=125) (actual time=0.013..0.013 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
10. 0.000 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.42..25.52 rows=1 width=125) (actual time=0.013..0.013 rows=0 loops=1)

11. 0.002 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.28..25.16 rows=2 width=121) (actual time=0.013..0.013 rows=0 loops=1)

12. 0.000 0.011 ↓ 0.0 0 1

Append (cost=0.00..8.54 rows=2 width=100) (actual time=0.011..0.011 rows=0 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on amr_messagenew m1 (cost=0.00..0.00 rows=1 width=102) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((sent_date >= '2019-07-31 00:00:00+06'::timestamp with time zone) AND (sent_date <= '2019-07-31 23:59:59.999+06'::timestamp with time zone) AND (SubPlan 1))
14.          

SubPlan (forSeq Scan)

15. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.51 rows=100 width=1) (never executed)

16. 0.009 0.009 ↓ 0.0 0 1

Index Scan using amr_messagenew_y2019m07_sent_date_idx on amr_messagenew_y2019m07 m1_1 (cost=0.14..8.54 rows=1 width=98) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((sent_date >= '2019-07-31 00:00:00+06'::timestamp with time zone) AND (sent_date <= '2019-07-31 23:59:59.999+06'::timestamp with time zone))
  • Filter: (SubPlan 1)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using amr_device_dev_eui_75f2cd0e_like on amr_device d1 (cost=0.28..8.30 rows=1 width=21) (never executed)

  • Index Cond: ((dev_eui)::text = (m1.dev_eui)::text)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using amr_meterinline_device_id_696f855f on amr_meterinline mi1 (cost=0.14..0.17 rows=1 width=12) (never executed)

  • Index Cond: (device_id = d1.id)
  • Filter: (resource_id = 2)
19. 0.000 0.000 ↓ 0.0 0

Index Scan using amr_node_pkey on amr_node n1 (cost=0.41..8.02 rows=1 width=4) (never executed)

  • Index Cond: (id = p1.node_id)
  • Filter: ((id = 168033) OR (parent_id = 168033))
20. 0.000 0.000 ↓ 0.0 0

Subquery Scan on m2 (cost=0.00..8.62 rows=1 width=33) (never executed)

  • Filter: ((d1.dev_eui)::text = (m2.dev_eui)::text)
21. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.00..8.60 rows=2 width=33) (never executed)

  • Group Key: mess.dev_eui
22. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..8.56 rows=2 width=25) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..8.54 rows=2 width=25) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on amr_messagenew mess (cost=0.00..0.00 rows=1 width=25) (never executed)

  • Filter: ((NOT ignore) AND (sent_date >= '2019-07-31 00:00:00+06'::timestamp with time zone) AND (sent_date <= '2019-07-31 23:59:59.999+06'::timestamp with time zone) AND ((dev_eui)::text = (d1.dev_eui)::text) AND (SubPlan 2))
25.          

SubPlan (forSeq Scan)

26. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.51 rows=100 width=1) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using amr_messagenew_y2019m07_sent_date_idx on amr_messagenew_y2019m07 mess_1 (cost=0.14..8.54 rows=1 width=25) (never executed)

  • Index Cond: ((sent_date >= '2019-07-31 00:00:00+06'::timestamp with time zone) AND (sent_date <= '2019-07-31 23:59:59.999+06'::timestamp with time zone))
  • Filter: ((NOT ignore) AND ((dev_eui)::text = (d1.dev_eui)::text) AND (SubPlan 2))
28. 0.001 0.216 ↓ 0.0 0 1

Unique (cost=215.47..215.48 rows=1 width=132) (actual time=0.216..0.216 rows=0 loops=1)

29. 0.010 0.215 ↓ 0.0 0 1

Sort (cost=215.47..215.47 rows=1 width=132) (actual time=0.215..0.215 rows=0 loops=1)

  • Sort Key: n1_1.id, m1_2.dev_eui, m1_2.sent_date DESC
  • Sort Method: quicksort Memory: 25kB
30. 0.000 0.205 ↓ 0.0 0 1

Nested Loop (cost=5.96..215.46 rows=1 width=132) (actual time=0.205..0.205 rows=0 loops=1)

31. 0.001 0.205 ↓ 0.0 0 1

Nested Loop (cost=5.96..210.78 rows=1 width=50) (actual time=0.205..0.205 rows=0 loops=1)

32. 0.005 0.158 ↓ 2.0 2 1

Nested Loop (cost=5.96..41.39 rows=1 width=25) (actual time=0.131..0.158 rows=2 loops=1)

33. 0.005 0.143 ↓ 2.0 2 1

Nested Loop (cost=5.68..33.08 rows=1 width=12) (actual time=0.124..0.143 rows=2 loops=1)

34. 0.040 0.106 ↓ 1.3 4 1

Hash Join (cost=5.26..9.00 rows=3 width=12) (actual time=0.100..0.106 rows=4 loops=1)

  • Hash Cond: (p1_1.id = mi1_1.point_id)
35. 0.020 0.020 ↓ 1.0 108 1

Seq Scan on amr_point p1_1 (cost=0.00..3.05 rows=105 width=8) (actual time=0.007..0.020 rows=108 loops=1)

36. 0.007 0.046 ↓ 1.3 4 1

Hash (cost=5.22..5.22 rows=3 width=12) (actual time=0.046..0.046 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.039 0.039 ↓ 1.3 4 1

Seq Scan on amr_meterinline mi1_1 (cost=0.00..5.22 rows=3 width=12) (actual time=0.007..0.039 rows=4 loops=1)

  • Filter: (resource_id = 2)
  • Rows Removed by Filter: 95
38. 0.032 0.032 ↓ 0.0 0 4

Index Scan using amr_node_pkey on amr_node n1_1 (cost=0.41..8.02 rows=1 width=4) (actual time=0.007..0.008 rows=0 loops=4)

  • Index Cond: (id = p1_1.node_id)
  • Filter: ((id = 168033) OR (parent_id = 168033))
  • Rows Removed by Filter: 0
39. 0.010 0.010 ↑ 1.0 1 2

Index Scan using amr_device_pkey on amr_device d1_1 (cost=0.28..8.30 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (id = mi1_1.device_id)
40. 0.002 0.046 ↓ 0.0 0 2

Subquery Scan on m2_1 (cost=0.00..169.38 rows=1 width=25) (actual time=0.023..0.023 rows=0 loops=2)

  • Filter: ((d1_1.dev_eui)::text = (m2_1.dev_eui)::text)
41. 0.000 0.044 ↓ 0.0 0 2

GroupAggregate (cost=0.00..168.25 rows=90 width=25) (actual time=0.022..0.022 rows=0 loops=2)

  • Group Key: mess_2.dev_eui
42. 0.004 0.044 ↓ 0.0 0 2

Result (cost=0.00..166.78 rows=115 width=25) (actual time=0.022..0.022 rows=0 loops=2)

43. 0.010 0.040 ↓ 0.0 0 2

Append (cost=0.00..165.63 rows=115 width=25) (actual time=0.020..0.020 rows=0 loops=2)

44. 0.002 0.002 ↓ 0.0 0 2

Seq Scan on amr_messagenew mess_2 (cost=0.00..0.00 rows=1 width=25) (actual time=0.001..0.001 rows=0 loops=2)

  • Filter: ((NOT ignore) AND (sent_date >= '2019-06-01 06:00:00+06'::timestamp with time zone) AND (sent_date < '2019-07-31 00:00:00+06'::timestamp with time zone) AND ((dev_eui)::text = (d1_1.dev_eui)::text) AND (SubPlan 4))
45.          

SubPlan (forSeq Scan)

46. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.51 rows=100 width=1) (never executed)

47. 0.004 0.018 ↓ 0.0 0 2

Bitmap Heap Scan on amr_messagenew_y2019m06 mess_3 (cost=9.55..127.33 rows=84 width=25) (actual time=0.009..0.009 rows=0 loops=2)

  • Recheck Cond: ((dev_eui)::text = (d1_1.dev_eui)::text)
  • Filter: ((NOT ignore) AND (sent_date >= '2019-06-01 06:00:00+06'::timestamp with time zone) AND (sent_date < '2019-07-31 00:00:00+06'::timestamp with time zone) AND (SubPlan 4))
48. 0.014 0.014 ↓ 0.0 0 2

Bitmap Index Scan on amr_messagenew_y2019m06_dev_eui_idx1 (cost=0.00..9.53 rows=168 width=0) (actual time=0.007..0.007 rows=0 loops=2)

  • Index Cond: ((dev_eui)::text = (d1_1.dev_eui)::text)
49. 0.010 0.010 ↓ 0.0 0 2

Index Scan using amr_messagenew_y2019m07_dev_eui_idx on amr_messagenew_y2019m07 mess_4 (cost=0.14..38.29 rows=30 width=25) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: ((dev_eui)::text = (d1_1.dev_eui)::text)
  • Filter: ((NOT ignore) AND (sent_date >= '2019-06-01 06:00:00+06'::timestamp with time zone) AND (sent_date < '2019-07-31 00:00:00+06'::timestamp with time zone) AND (SubPlan 4))
50. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..4.65 rows=3 width=124) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Seq Scan on amr_messagenew m1_2 (cost=0.00..0.00 rows=1 width=102) (never executed)

  • Filter: ((sent_date >= '2019-06-01 06:00:00+06'::timestamp with time zone) AND (sent_date < '2019-07-31 00:00:00+06'::timestamp with time zone) AND ((m2_1.dev_eui)::text = (dev_eui)::text) AND (m2_1.maxdate = sent_date) AND (SubPlan 3))
52.          

SubPlan (forSeq Scan)

53. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.51 rows=100 width=1) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Index Scan using amr_messagenew_y2019m06_sent_date_idx on amr_messagenew_y2019m06 m1_3 (cost=0.27..3.03 rows=1 width=130) (never executed)

  • Index Cond: ((sent_date = m2_1.maxdate) AND (sent_date >= '2019-06-01 06:00:00+06'::timestamp with time zone) AND (sent_date < '2019-07-31 00:00:00+06'::timestamp with time zone))
  • Filter: (((m2_1.dev_eui)::text = (dev_eui)::text) AND (SubPlan 3))
55. 0.000 0.000 ↓ 0.0 0

Index Scan using amr_messagenew_y2019m07_sent_date_idx on amr_messagenew_y2019m07 m1_4 (cost=0.14..1.62 rows=1 width=98) (never executed)

  • Index Cond: ((sent_date = m2_1.maxdate) AND (sent_date >= '2019-06-01 06:00:00+06'::timestamp with time zone) AND (sent_date < '2019-07-31 00:00:00+06'::timestamp with time zone))
  • Filter: (((m2_1.dev_eui)::text = (dev_eui)::text) AND (SubPlan 3))