explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RynE : new_events as is

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 90.615 2,340.864 ↑ 75.4 68,309 1

Sort (cost=9,035,387.03..9,048,267.51 rows=5,152,192 width=682) (actual time=2,328.862..2,340.864 rows=68,309 loops=1)

  • Sort Key: e.id
  • Sort Method: external sort Disk: 15864kB
2. 30.729 2,250.249 ↑ 75.4 68,309 1

Merge Join (cost=5,079,387.05..5,326,415.22 rows=5,152,192 width=682) (actual time=2,200.383..2,250.249 rows=68,309 loops=1)

  • Merge Cond: (e.vehicle_id = v.vehicle_id)
3. 50.936 1,910.464 ↓ 1.2 68,401 1

Sort (cost=4,713,819.40..4,713,966.11 rows=58,686 width=1,636) (actual time=1,899.898..1,910.464 rows=68,401 loops=1)

  • Sort Key: e.vehicle_id
  • Sort Method: quicksort Memory: 12546kB
4. 18.364 1,859.528 ↓ 1.2 68,401 1

Hash Left Join (cost=493.72..4,667,447.75 rows=58,686 width=1,636) (actual time=13.555..1,859.528 rows=68,401 loops=1)

  • Hash Cond: (e.operator_id = o.id)
5. 95.263 1,828.167 ↓ 1.2 68,401 1

Nested Loop Left Join (cost=0.00..4,616,016.68 rows=58,686 width=84) (actual time=0.509..1,828.167 rows=68,401 loops=1)

6. 6.010 159.681 ↓ 1.2 68,401 1

Append (cost=0.00..29,624.98 rows=58,686 width=76) (actual time=0.445..159.681 rows=68,401 loops=1)

7. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on event e (cost=0.00..0.00 rows=1 width=76) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((NOT acknowledged) AND (event_time_utc > '2019-01-31 03:57:14.72694'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
8. 113.211 113.211 ↓ 1.2 51,032 1

Index Scan using i_event_2019m02_unread on event_2019m02 e_1 (cost=0.41..21,465.85 rows=42,958 width=76) (actual time=0.444..113.211 rows=51,032 loops=1)

  • Filter: (event_time_utc > '2019-01-31 03:57:14.72694'::timestamp without time zone)
9. 0.030 3.632 ↑ 92.2 8 1

Bitmap Heap Scan on event_2019m01 e_2 (cost=1,058.70..3,827.41 rows=738 width=76) (actual time=3.617..3.632 rows=8 loops=1)

  • Recheck Cond: ((event_time_utc > '2019-01-31 03:57:14.72694'::timestamp without time zone) AND (NOT acknowledged) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Heap Blocks: exact=7
10. 0.725 3.602 ↓ 0.0 0 1

BitmapAnd (cost=1,058.70..1,058.70 rows=738 width=0) (actual time=3.602..3.602 rows=0 loops=1)

11. 2.703 2.703 ↓ 1.2 22,534 1

Bitmap Index Scan on i_event_2019m01_ts (cost=0.00..369.79 rows=18,849 width=0) (actual time=2.703..2.703 rows=22,534 loops=1)

  • Index Cond: (event_time_utc > '2019-01-31 03:57:14.72694'::timestamp without time zone)
12. 0.174 0.174 ↑ 61.9 500 1

Bitmap Index Scan on i_event_2019m01_unread (cost=0.00..688.29 rows=30,933 width=0) (actual time=0.174..0.174 rows=500 loops=1)

13. 36.827 36.827 ↓ 1.2 17,361 1

Index Scan using i_event_2019m03_unread on event_2019m03 e_3 (cost=0.29..4,331.71 rows=14,989 width=76) (actual time=0.012..36.827 rows=17,361 loops=1)

  • Filter: (event_time_utc > '2019-01-31 03:57:14.72694'::timestamp without time zone)
14. 410.406 1,573.223 ↑ 15.0 1 68,401

Append (cost=0.00..78.00 rows=15 width=16) (actual time=0.015..0.023 rows=1 loops=68,401)

15. 0.000 0.000 ↓ 0.0 0 68,401

Seq Scan on shift_record sr (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=68,401)

  • Filter: (event_id = e.id)
16. 205.203 205.203 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2019m02 on shift_record_2019m02 sr_1 (cost=0.43..6.25 rows=1 width=16) (actual time=0.002..0.003 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
17. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m12 on shift_record_2018m12 sr_2 (cost=0.43..6.27 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
18. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m02 on shift_record_2018m02 sr_3 (cost=0.43..4.94 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
19. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2019m01 on shift_record_2019m01 sr_4 (cost=0.43..6.32 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
20. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m03 on shift_record_2018m03 sr_5 (cost=0.43..4.76 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
21. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m08 on shift_record_2018m08 sr_6 (cost=0.43..5.69 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
22. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m04 on shift_record_2018m04 sr_7 (cost=0.43..4.83 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
23. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m11 on shift_record_2018m11 sr_8 (cost=0.43..6.27 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
24. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m05 on shift_record_2018m05 sr_9 (cost=0.43..5.07 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
25. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m10 on shift_record_2018m10 sr_10 (cost=0.43..6.24 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
26. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m06 on shift_record_2018m06 sr_11 (cost=0.43..5.31 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
27. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m07 on shift_record_2018m07 sr_12 (cost=0.43..5.55 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
28. 68.401 68.401 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2018m09 on shift_record_2018m09 sr_13 (cost=0.43..5.72 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
29. 136.802 136.802 ↓ 0.0 0 68,401

Index Scan using pk_shift_record_2019m03 on shift_record_2019m03 sr_14 (cost=0.43..4.77 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=68,401)

  • Index Cond: (event_id = e.id)
30. 1.570 12.997 ↑ 1.0 6,773 1

Hash (cost=407.92..407.92 rows=6,864 width=28) (actual time=12.997..12.997 rows=6,773 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 469kB
31. 11.427 11.427 ↑ 1.0 6,773 1

Foreign Scan on operator o (cost=100.00..407.92 rows=6,864 width=28) (actual time=0.304..11.427 rows=6,773 loops=1)

32. 5.096 309.056 ↑ 9.0 89,119 1

Materialize (cost=365,567.65..369,578.89 rows=802,248 width=185) (actual time=300.422..309.056 rows=89,119 loops=1)

33. 12.709 303.960 ↑ 37.0 21,710 1

Sort (cost=365,567.65..367,573.27 rows=802,248 width=185) (actual time=300.416..303.960 rows=21,710 loops=1)

  • Sort Key: v.vehicle_id
  • Sort Method: quicksort Memory: 4236kB
34. 291.251 291.251 ↑ 37.0 21,710 1

Foreign Scan on mvw_vehicles v (cost=100.00..212,853.44 rows=802,248 width=185) (actual time=197.011..291.251 rows=21,710 loops=1)