explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uGUb : Optimization for: plan #2EKj

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 18.506 90.979 ↑ 3.4 11,106 1

Hash Join (cost=426.88..4,735.32 rows=38,256 width=40) (actual time=13.421..90.979 rows=11,106 loops=1)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id, dh.history_dtt
  • Inner Unique: true
  • Hash Cond: (dh.device_id = dev.id)
  • Buffers: shared hit=2,642 read=4
2. 9.177 59.078 ↓ 1.0 38,313 1

Append (cost=0.00..4,207.98 rows=38,256 width=16) (actual time=0.008..59.078 rows=38,313 loops=1)

  • Buffers: shared hit=2,465 read=4
3. 0.010 0.010 ↓ 3.0 3 1

Seq Scan on users.device_history_p2020_02 dh (cost=0.00..1.04 rows=1 width=16) (actual time=0.008..0.010 rows=3 loops=1)

  • Output: dh.history_dtt, dh.device_id
  • Filter: ((dh.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=1
4. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on users.device_history_p2020_03 dh_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=1)

  • Output: dh_1.history_dtt, dh_1.device_id
  • Filter: ((dh_1.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_1.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=1
5. 0.004 0.004 ↓ 2.0 2 1

Seq Scan on users.device_history_p2020_04 dh_2 (cost=0.00..1.03 rows=1 width=16) (actual time=0.003..0.004 rows=2 loops=1)

  • Output: dh_2.history_dtt, dh_2.device_id
  • Filter: ((dh_2.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_2.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=1
6. 45.819 45.819 ↓ 1.0 33,271 1

Seq Scan on users.device_history_p2020_05 dh_3 (cost=0.00..3,750.56 rows=33,216 width=16) (actual time=0.007..45.819 rows=33,271 loops=1)

  • Output: dh_3.history_dtt, dh_3.device_id
  • Filter: ((dh_3.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_3.device_event_type)::text = 'ADDITION'::text))
  • Rows Removed by Filter: 61,433
  • Buffers: shared hit=2,330
7. 0.421 0.421 ↑ 1.0 8 1

Seq Scan on users.device_history_p2020_06 dh_4 (cost=0.00..48.24 rows=8 width=16) (actual time=0.009..0.421 rows=8 loops=1)

  • Output: dh_4.history_dtt, dh_4.device_id
  • Filter: ((dh_4.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_4.device_event_type)::text = 'ADDITION'::text))
  • Rows Removed by Filter: 1,008
  • Buffers: shared hit=33
8. 0.169 0.169 ↓ 0.0 0 1

Index Only Scan using device_history_p2020_07_history_dtt_device_event_type_devic_idx on users.device_history_p2020_07 dh_5 (cost=0.28..42.38 rows=1 width=16) (actual time=0.169..0.169 rows=0 loops=1)

  • Output: dh_5.history_dtt, dh_5.device_id
  • Index Cond: ((dh_5.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND (dh_5.device_event_type = 'ADDITION'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=2 read=4
9. 3.472 3.472 ↑ 1.0 5,028 1

Seq Scan on users.device_history_default dh_6 (cost=0.00..172.42 rows=5,028 width=16) (actual time=0.009..3.472 rows=5,028 loops=1)

  • Output: dh_6.history_dtt, dh_6.device_id
  • Filter: ((dh_6.history_dtt < '2020-08-01 00:00:00'::timestamp without time zone) AND ((dh_6.device_event_type)::text = 'ADDITION'::text))
  • Buffers: shared hit=97
10. 7.284 13.395 ↑ 1.0 11,106 1

Hash (cost=288.06..288.06 rows=11,106 width=32) (actual time=13.395..13.395 rows=11,106 loops=1)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id
  • Buckets: 16,384 Batches: 1 Memory Usage: 803kB
  • Buffers: shared hit=177
11. 6.111 6.111 ↑ 1.0 11,106 1

Seq Scan on users.device dev (cost=0.00..288.06 rows=11,106 width=32) (actual time=0.007..6.111 rows=11,106 loops=1)

  • Output: dev.id, dev.org_id, dev.device_id, dev.serial_number, dev.type_id
  • Buffers: shared hit=177