explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5tSP : Optimization for: plan #uexn

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.561 328.166 ↑ 12.8 4,213 1

Sort (cost=338,163.23..338,297.85 rows=53,847 width=682) (actual time=327.440..328.166 rows=4,213 loops=1)

  • Sort Key: e.id
  • Sort Method: quicksort Memory: 2308kB
2. 4.352 324.605 ↑ 12.8 4,213 1

Hash Join (cost=183,482.89..317,548.27 rows=53,847 width=682) (actual time=224.639..324.605 rows=4,213 loops=1)

  • Hash Cond: (v.vehicle_id = e.vehicle_id)
3. 177.253 177.253 ↑ 15.0 21,707 1

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

4. 2.060 143.000 ↓ 2.8 4,213 1

Hash (cost=183,363.97..183,363.97 rows=1,513 width=1,636) (actual time=143.000..143.000 rows=4,213 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 604kB
5. 1.278 140.940 ↓ 2.8 4,213 1

Hash Left Join (cost=493.72..183,363.97 rows=1,513 width=1,636) (actual time=15.971..140.940 rows=4,213 loops=1)

  • Hash Cond: (e.operator_id = o.id)
6. 2.362 123.912 ↓ 2.8 4,213 1

Nested Loop Left Join (cost=0.00..181,557.02 rows=1,513 width=84) (actual time=0.180..123.912 rows=4,213 loops=1)

7. 0.352 28.864 ↓ 2.8 4,213 1

Append (cost=0.00..5,423.70 rows=1,513 width=76) (actual time=0.094..28.864 rows=4,213 loops=1)

8. 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:43.278732'::timestamp without time zone) AND (create_time_utc > '2019-03-11 02:29:43.278732'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
9. 0.285 0.285 ↑ 1.6 74 1

Index Scan using i_event_2019m02_create_time on event_2019m02 e_1 (cost=0.42..1,126.92 rows=121 width=76) (actual time=0.091..0.285 rows=74 loops=1)

  • Index Cond: (create_time_utc > '2019-03-11 02:29:43.278732'::timestamp without time zone)
  • Filter: ((NOT acknowledged) AND (event_time_utc > '2019-01-31 02:29:43.278732'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Rows Removed by Filter: 32
10. 0.028 0.028 ↓ 0.0 0 1

Index Scan using i_event_2019m01_create_time on event_2019m01 e_2 (cost=0.42..102.17 rows=1 width=76) (actual time=0.028..0.028 rows=0 loops=1)

  • Index Cond: (create_time_utc > '2019-03-11 02:29:43.278732'::timestamp without time zone)
  • Filter: ((NOT acknowledged) AND (event_time_utc > '2019-01-31 02:29:43.278732'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Rows Removed by Filter: 7
11. 28.197 28.197 ↓ 3.0 4,139 1

Index Scan using i_event_2019m03_unread on event_2019m03 e_3 (cost=0.29..4,194.61 rows=1,390 width=76) (actual time=19.653..28.197 rows=4,139 loops=1)

  • Filter: ((event_time_utc > '2019-01-31 02:29:43.278732'::timestamp without time zone) AND (create_time_utc > '2019-03-11 02:29:43.278732'::timestamp without time zone))
  • Rows Removed by Filter: 12769
12. 25.278 92.686 ↑ 15.0 1 4,213

Append (cost=0.00..116.26 rows=15 width=16) (actual time=0.022..0.022 rows=1 loops=4,213)

13. 0.000 0.000 ↓ 0.0 0 4,213

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

  • Filter: (event_id = e.id)
14. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2019m02 on shift_record_2019m02 sr_1 (cost=0.43..8.35 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
15. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m12 on shift_record_2018m12 sr_2 (cost=0.43..8.35 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
16. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m02 on shift_record_2018m02 sr_3 (cost=0.43..8.25 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
17. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2019m01 on shift_record_2019m01 sr_4 (cost=0.43..8.35 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
18. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m03 on shift_record_2018m03 sr_5 (cost=0.43..8.26 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
19. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m08 on shift_record_2018m08 sr_6 (cost=0.43..8.33 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
20. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m04 on shift_record_2018m04 sr_7 (cost=0.43..8.26 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
21. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m11 on shift_record_2018m11 sr_8 (cost=0.43..8.35 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
22. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m05 on shift_record_2018m05 sr_9 (cost=0.43..8.28 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
23. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m10 on shift_record_2018m10 sr_10 (cost=0.43..8.35 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
24. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m06 on shift_record_2018m06 sr_11 (cost=0.43..8.30 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
25. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m07 on shift_record_2018m07 sr_12 (cost=0.43..8.32 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
26. 4.213 4.213 ↓ 0.0 0 4,213

Index Scan using pk_shift_record_2018m09 on shift_record_2018m09 sr_13 (cost=0.43..8.33 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,213)

  • Index Cond: (event_id = e.id)
27. 12.639 12.639 ↑ 1.0 1 4,213

Index Scan using pk_shift_record_2019m03 on shift_record_2019m03 sr_14 (cost=0.43..8.18 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4,213)

  • Index Cond: (event_id = e.id)
28. 1.844 15.750 ↑ 1.0 6,773 1

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

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

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

Planning time : 20.343 ms
Execution time : 329.137 ms