explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BDCP : Optimization for: plan #hsWS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.484 316.084 ↑ 12.8 4,215 1

Sort (cost=333,995.28..334,129.98 rows=53,883 width=650) (actual time=315.425..316.084 rows=4,215 loops=1)

  • Sort Key: e.id
  • Sort Method: quicksort Memory: 2309kB
2. 3.980 312.600 ↑ 12.8 4,215 1

Hash Join (cost=181,789.51..314,105.23 rows=53,883 width=650) (actual time=202.689..312.600 rows=4,215 loops=1)

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

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

4. 2.025 118.728 ↓ 2.8 4,215 1

Hash (cost=181,670.59..181,670.59 rows=1,514 width=84) (actual time=118.728..118.728 rows=4,215 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 604kB
5. 4.777 116.703 ↓ 2.8 4,215 1

Nested Loop Left Join (cost=0.00..181,670.59 rows=1,514 width=84) (actual time=0.198..116.703 rows=4,215 loops=1)

6. 0.352 27.626 ↓ 2.8 4,215 1

Append (cost=0.00..5,418.79 rows=1,514 width=76) (actual time=0.079..27.626 rows=4,215 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:28:19.591211'::timestamp without time zone) AND (create_time_utc > '2019-03-11 02:28:19.591211'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
8. 0.236 0.236 ↑ 1.6 74 1

Index Scan using i_event_2019m02_create_time on event_2019m02 e_1 (cost=0.42..1,127.76 rows=121 width=76) (actual time=0.076..0.236 rows=74 loops=1)

  • Index Cond: (create_time_utc > '2019-03-11 02:28:19.591211'::timestamp without time zone)
  • Filter: ((NOT acknowledged) AND (event_time_utc > '2019-01-31 02:28:19.591211'::timestamp without time zone) AND (confirmation_state = 'pending'::enum_confirmation_state))
  • Rows Removed by Filter: 32
9. 0.023 0.023 ↓ 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.023..0.023 rows=0 loops=1)

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

Index Scan using i_event_2019m03_unread on event_2019m03 e_3 (cost=0.29..4,188.87 rows=1,391 width=76) (actual time=18.776..27.013 rows=4,141 loops=1)

  • Filter: ((event_time_utc > '2019-01-31 02:28:19.591211'::timestamp without time zone) AND (create_time_utc > '2019-03-11 02:28:19.591211'::timestamp without time zone))
  • Rows Removed by Filter: 12764
11. 16.860 84.300 ↑ 15.0 1 4,215

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

12. 0.000 0.000 ↓ 0.0 0 4,215

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

  • Filter: (event_id = e.id)
13. 4.215 4.215 ↓ 0.0 0 4,215

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Index Cond: (event_id = e.id)
26. 12.645 12.645 ↑ 1.0 1 4,215

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

  • Index Cond: (event_id = e.id)