explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uexn

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 89.968 2,142.199 ↑ 30.5 67,996 1

Sort (cost=5,782,340.95..5,787,522.60 rows=2,072,662 width=682) (actual time=2,130.460..2,142.199 rows=67,996 loops=1)

  • Sort Key: e.id
  • Sort Method: external sort Disk: 15928kB
2. 30.541 2,052.231 ↑ 30.5 67,996 1

Merge Join (cost=4,834,827.38..4,934,383.06 rows=2,072,662 width=682) (actual time=2,001.848..2,052.231 rows=67,996 loops=1)

  • Merge Cond: (e.vehicle_id = v.vehicle_id)
3. 48.404 1,801.494 ↓ 1.2 68,088 1

Sort (cost=4,688,689.56..4,688,835.16 rows=58,238 width=1,636) (actual time=1,790.683..1,801.494 rows=68,088 loops=1)

  • Sort Key: e.vehicle_id
  • Sort Method: quicksort Memory: 12502kB
4. 17.340 1,753.090 ↓ 1.2 68,088 1

Hash Left Join (cost=493.72..4,642,675.12 rows=58,238 width=1,636) (actual time=16.449..1,753.090 rows=68,088 loops=1)

  • Hash Cond: (e.operator_id = o.id)
5. 69.519 1,720.129 ↓ 1.2 68,088 1

Nested Loop Left Join (cost=0.00..4,591,632.90 rows=58,238 width=84) (actual time=0.780..1,720.129 rows=68,088 loops=1)

6. 5.382 152.674 ↓ 1.2 68,088 1

Append (cost=0.00..29,662.63 rows=58,238 width=76) (actual time=0.673..152.674 rows=68,088 loops=1)

7. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((NOT acknowledged) AND (event_time_utc > '2019-01-31 02:29:20.584865'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
8. 108.507 108.507 ↓ 1.2 51,174 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.670..108.507 rows=51,174 loops=1)

  • Filter: (event_time_utc > '2019-01-31 02:29:20.584865'::timestamp without time zone)
9. 0.039 3.966 ↑ 79.0 10 1

Bitmap Heap Scan on event_2019m01 e_2 (cost=1,084.75..4,038.55 rows=790 width=76) (actual time=3.945..3.966 rows=10 loops=1)

  • Recheck Cond: ((event_time_utc > '2019-01-31 02:29:20.584865'::timestamp without time zone) AND (NOT acknowledged) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Heap Blocks: exact=9
10. 0.765 3.927 ↓ 0.0 0 1

BitmapAnd (cost=1,084.75..1,084.75 rows=790 width=0) (actual time=3.927..3.927 rows=0 loops=1)

11. 2.979 2.979 ↓ 1.2 24,338 1

Bitmap Index Scan on i_event_2019m01_ts (cost=0.00..395.82 rows=20,186 width=0) (actual time=2.979..2.979 rows=24,338 loops=1)

  • Index Cond: (event_time_utc > '2019-01-31 02:29:20.584865'::timestamp without time zone)
12. 0.183 0.183 ↑ 61.3 505 1

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

13. 34.817 34.817 ↓ 1.2 16,904 1

Index Scan using i_event_2019m03_unread on event_2019m03 e_3 (cost=0.29..4,158.24 rows=14,489 width=76) (actual time=0.012..34.817 rows=16,904 loops=1)

  • Filter: (event_time_utc > '2019-01-31 02:29:20.584865'::timestamp without time zone)
14. 408.528 1,497.936 ↑ 15.0 1 68,088

Append (cost=0.00..78.18 rows=15 width=16) (actual time=0.014..0.022 rows=1 loops=68,088)

15. 0.000 0.000 ↓ 0.0 0 68,088

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,088)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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,088)

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

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

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

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

32. 5.323 220.196 ↑ 3.7 88,822 1

Materialize (cost=146,137.82..147,763.91 rows=325,217 width=185) (actual time=211.012..220.196 rows=88,822 loops=1)

33. 12.689 214.873 ↑ 15.0 21,707 1

Sort (cost=146,137.82..146,950.87 rows=325,217 width=185) (actual time=211.003..214.873 rows=21,707 loops=1)

  • Sort Key: v.vehicle_id
  • Sort Method: quicksort Memory: 4243kB
34. 202.184 202.184 ↑ 15.0 21,707 1

Foreign Scan on mvw_vehicles v (cost=100.00..86,346.51 rows=325,217 width=185) (actual time=104.408..202.184 rows=21,707 loops=1)