explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 38Rc : new_events limited by registration time

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.175 1,288.054 ↑ 32.2 4,502 1

Sort (cost=439,276.81..439,638.96 rows=144,858 width=682) (actual time=1,287.596..1,288.054 rows=4,502 loops=1)

  • Sort Key: e.id
  • Sort Method: quicksort Memory: 2455kB
2. 191.010 1,280.879 ↑ 32.2 4,502 1

Nested Loop (cost=594.03..382,790.88 rows=144,858 width=682) (actual time=15.170..1,280.879 rows=4,502 loops=1)

3. 2.990 211.979 ↓ 2.7 4,502 1

Hash Left Join (cost=493.72..199,289.00 rows=1,650 width=1,636) (actual time=14.854..211.979 rows=4,502 loops=1)

  • Hash Cond: (e.operator_id = o.id)
4. 9.545 194.455 ↓ 2.7 4,502 1

Nested Loop Left Join (cost=0.00..197,363.14 rows=1,650 width=84) (actual time=0.270..194.455 rows=4,502 loops=1)

5. 0.826 36.344 ↓ 2.7 4,502 1

Append (cost=0.00..5,587.76 rows=1,650 width=76) (actual time=0.224..36.344 rows=4,502 loops=1)

6. 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:58.98375'::timestamp without time zone) AND (create_time_utc > '2019-03-11 03:57:58.98375'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
7. 0.236 0.236 ↑ 60.0 2 1

Index Scan using i_event_2019m02_create_time on event_2019m02 e_1 (cost=0.42..1,122.64 rows=120 width=76) (actual time=0.222..0.236 rows=2 loops=1)

  • Index Cond: (create_time_utc > '2019-03-11 03:57:58.98375'::timestamp without time zone)
  • Filter: ((NOT acknowledged) AND (event_time_utc > '2019-01-31 03:57:58.98375'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Rows Removed by Filter: 113
8. 0.031 0.031 ↓ 0.0 0 1

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

  • Index Cond: (create_time_utc > '2019-03-11 03:57:58.98375'::timestamp without time zone)
  • Filter: ((NOT acknowledged) AND (event_time_utc > '2019-01-31 03:57:58.98375'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Rows Removed by Filter: 7
9. 35.250 35.250 ↓ 2.9 4,500 1

Index Scan using i_event_2019m03_unread on event_2019m03 e_3 (cost=0.29..4,369.19 rows=1,528 width=76) (actual time=19.847..35.250 rows=4,500 loops=1)

  • Filter: ((event_time_utc > '2019-01-31 03:57:58.98375'::timestamp without time zone) AND (create_time_utc > '2019-03-11 03:57:58.98375'::timestamp without time zone))
  • Rows Removed by Filter: 12865
10. 9.004 148.566 ↑ 15.0 1 4,502

Append (cost=0.00..116.08 rows=15 width=16) (actual time=0.033..0.033 rows=1 loops=4,502)

11. 0.000 0.000 ↓ 0.0 0 4,502

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

  • Filter: (event_id = e.id)
12. 13.506 13.506 ↓ 0.0 0 4,502

Index Scan using pk_shift_record_2019m02 on shift_record_2019m02 sr_1 (cost=0.43..8.34 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=4,502)

  • Index Cond: (event_id = e.id)
13. 9.004 9.004 ↓ 0.0 0 4,502

Index Scan using pk_shift_record_2018m12 on shift_record_2018m12 sr_2 (cost=0.43..8.34 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

  • Index Cond: (event_id = e.id)
14. 9.004 9.004 ↓ 0.0 0 4,502

Index Scan using pk_shift_record_2018m02 on shift_record_2018m02 sr_3 (cost=0.43..8.24 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2019m01 on shift_record_2019m01 sr_4 (cost=0.43..8.34 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m03 on shift_record_2018m03 sr_5 (cost=0.43..8.25 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m08 on shift_record_2018m08 sr_6 (cost=0.43..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m04 on shift_record_2018m04 sr_7 (cost=0.43..8.25 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m11 on shift_record_2018m11 sr_8 (cost=0.43..8.34 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m05 on shift_record_2018m05 sr_9 (cost=0.43..8.27 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m10 on shift_record_2018m10 sr_10 (cost=0.43..8.34 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m06 on shift_record_2018m06 sr_11 (cost=0.43..8.29 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m07 on shift_record_2018m07 sr_12 (cost=0.43..8.30 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

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

Index Scan using pk_shift_record_2018m09 on shift_record_2018m09 sr_13 (cost=0.43..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,502)

  • Index Cond: (event_id = e.id)
25. 18.008 18.008 ↑ 1.0 1 4,502

Index Scan using pk_shift_record_2019m03 on shift_record_2019m03 sr_14 (cost=0.43..8.15 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=4,502)

  • Index Cond: (event_id = e.id)
26. 1.583 14.534 ↑ 1.0 6,773 1

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

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

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

28. 877.890 877.890 ↑ 1.0 1 4,502

Foreign Scan on mvw_vehicles v (cost=100.31..108.35 rows=1 width=185) (actual time=0.195..0.195 rows=1 loops=4,502)