explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hsWS

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 91.106 2,058.945 ↑ 30.5 68,023 1

Sort (cost=5,593,850.36..5,599,031.57 rows=2,072,484 width=650) (actual time=2,046.740..2,058.945 rows=68,023 loops=1)

  • Sort Key: e.id
  • Sort Method: external sort Disk: 15936kB
2. 28.488 1,967.839 ↑ 30.5 68,023 1

Merge Join (cost=4,742,109.46..4,774,300.93 rows=2,072,484 width=650) (actual time=1,919.063..1,967.839 rows=68,023 loops=1)

  • Merge Cond: (e.vehicle_id = v.vehicle_id)
3. 47.344 1,728.747 ↓ 1.2 68,115 1

Sort (cost=4,595,971.64..4,596,117.22 rows=58,233 width=84) (actual time=1,717.730..1,728.747 rows=68,115 loops=1)

  • Sort Key: e.vehicle_id
  • Sort Method: quicksort Memory: 12505kB
4. 32.674 1,681.403 ↓ 1.2 68,115 1

Nested Loop Left Join (cost=0.00..4,591,362.63 rows=58,233 width=84) (actual time=0.357..1,681.403 rows=68,115 loops=1)

5. 5.669 150.199 ↓ 1.2 68,115 1

Append (cost=0.00..29,660.70 rows=58,233 width=76) (actual time=0.342..150.199 rows=68,115 loops=1)

6. 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:27:52.015891'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
7. 106.120 106.120 ↓ 1.2 51,196 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.340..106.120 rows=51,196 loops=1)

  • Filter: (event_time_utc > '2019-01-31 02:27:52.015891'::timestamp without time zone)
8. 0.042 3.906 ↑ 79.1 10 1

Bitmap Heap Scan on event_2019m01 e_2 (cost=1,084.93..4,042.34 rows=791 width=76) (actual time=3.884..3.906 rows=10 loops=1)

  • Recheck Cond: ((event_time_utc > '2019-01-31 02:27:52.015891'::timestamp without time zone) AND (NOT acknowledged) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Heap Blocks: exact=9
9. 0.748 3.864 ↓ 0.0 0 1

BitmapAnd (cost=1,084.93..1,084.93 rows=791 width=0) (actual time=3.864..3.864 rows=0 loops=1)

10. 2.911 2.911 ↓ 1.2 24,370 1

Bitmap Index Scan on i_event_2019m01_ts (cost=0.00..395.99 rows=20,209 width=0) (actual time=2.911..2.911 rows=24,370 loops=1)

  • Index Cond: (event_time_utc > '2019-01-31 02:27:52.015891'::timestamp without time zone)
11. 0.205 0.205 ↑ 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.205..0.205 rows=505 loops=1)

12. 34.502 34.502 ↓ 1.2 16,909 1

Index Scan using i_event_2019m03_unread on event_2019m03 e_3 (cost=0.29..4,152.51 rows=14,483 width=76) (actual time=0.011..34.502 rows=16,909 loops=1)

  • Filter: (event_time_utc > '2019-01-31 02:27:52.015891'::timestamp without time zone)
13. 408.690 1,498.530 ↑ 15.0 1 68,115

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

14. 0.000 0.000 ↓ 0.0 0 68,115

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

  • Filter: (event_id = e.id)
15. 136.230 136.230 ↓ 0.0 0 68,115

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

  • Index Cond: (event_id = e.id)
16. 68.115 68.115 ↓ 0.0 0 68,115

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Index Cond: (event_id = e.id)
29. 5.082 210.604 ↑ 3.7 88,845 1

Materialize (cost=146,137.82..147,763.91 rows=325,217 width=185) (actual time=201.169..210.604 rows=88,845 loops=1)

30. 13.280 205.522 ↑ 15.0 21,707 1

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

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

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