explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nCmX : Optimization for: plan #YrUR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 570.131 117,507.642 ↓ 2.7 116,675 1

Sort (cost=156,762.39..156,871.36 rows=43,590 width=183) (actual time=117,437.385..117,507.642 rows=116,675 loops=1)

  • Sort Key: tblbmc_emergency_alert_ects.d_gps_time_stamp
  • Sort Method: quicksort Memory: 34,289kB
  • Buffers: shared hit=24,131 read=64,944 written=1
2. 924.044 116,937.511 ↓ 2.7 116,675 1

Hash Join (cost=29,416.89..153,403.41 rows=43,590 width=183) (actual time=16,861.703..116,937.511 rows=116,675 loops=1)

  • Hash Cond: (tblbmc_emergency_alert_ects.i_shipment_activated_id = tbl_ects_shipment_activated.id)
  • Buffers: shared hit=24,128 read=64,944 written=1
3. 182.426 102,774.905 ↓ 2.7 116,755 1

Hash Join (cost=2,304.55..124,111.57 rows=43,590 width=124) (actual time=3,623.015..102,774.905 rows=116,755 loops=1)

  • Hash Cond: (tblbmc_emergency_alert_ects.i_event_id = tblbmc_bat_event.i_event_id)
  • Buffers: shared hit=2,666 read=64,944 written=1
4. 885.809 102,592.173 ↓ 2.7 116,755 1

Hash Join (cost=2,287.54..123,440.71 rows=43,590 width=98) (actual time=3,622.054..102,592.173 rows=116,755 loops=1)

  • Hash Cond: ((tblbmc_emergency_alert_ects.v_bat_id)::bpchar = tblbmc_bat.v_bat_id)
  • Buffers: shared hit=2,656 read=64,944 written=1
5. 100,941.935 100,941.935 ↓ 1.4 116,755 1

Seq Scan on tblbmc_emergency_alert_ects (cost=0.00..119,429.34 rows=85,862 width=87) (actual time=2,857.565..100,941.935 rows=116,755 loops=1)

  • Filter: ((i_status = 0) AND (d_gps_time_stamp > (now() - '3 days'::interval)))
  • Rows Removed by Filter: 2,574,484
  • Buffers: shared hit=1,089 read=64,944 written=1
6. 4.811 764.429 ↓ 1.5 14,796 1

Hash (cost=2,165.55..2,165.55 rows=9,759 width=20) (actual time=764.429..764.429 rows=14,796 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 755kB
  • Buffers: shared hit=1,564
7. 106.069 759.618 ↓ 1.5 14,796 1

Hash Join (cost=4.83..2,165.55 rows=9,759 width=20) (actual time=0.576..759.618 rows=14,796 loops=1)

  • Hash Cond: (tblbmc_bat.c_user_group_rid = tblbmc_provider.c_user_group_rid)
  • Join Filter: ((tblbmc_bat.c_user_group_rid = '4AA88976-F086-739E-740D8DB01D020D6F'::bpchar) OR (tblbmc_provider.c_parent_rid = '4AA88976-F086-739E-740D8DB01D020D6F'::bpchar) OR (tblbmc_provider.c_group_rid = '71BD3757-0624-31BB-73BF81BDEE6FBFCF'::bpchar))
  • Rows Removed by Join Filter: 4,403
  • Buffers: shared hit=1,564
8. 653.003 653.003 ↑ 1.0 19,223 1

Seq Scan on tblbmc_bat (cost=0.00..1,752.23 rows=19,223 width=56) (actual time=0.013..653.003 rows=19,223 loops=1)

  • Buffers: shared hit=1,560
9. 0.014 0.546 ↑ 1.0 37 1

Hash (cost=4.37..4.37 rows=37 width=108) (actual time=0.546..0.546 rows=37 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 4kB
  • Buffers: shared hit=4
10. 0.532 0.532 ↑ 1.0 37 1

Seq Scan on tblbmc_provider (cost=0.00..4.37 rows=37 width=108) (actual time=0.008..0.532 rows=37 loops=1)

  • Buffers: shared hit=4
11. 0.157 0.306 ↑ 1.0 445 1

Hash (cost=11.45..11.45 rows=445 width=30) (actual time=0.306..0.306 rows=445 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
  • Buffers: shared hit=7
12. 0.149 0.149 ↑ 1.0 445 1

Seq Scan on tblbmc_bat_event (cost=0.00..11.45 rows=445 width=30) (actual time=0.009..0.149 rows=445 loops=1)

  • Buffers: shared hit=7
13. 1,252.814 13,238.562 ↓ 1.0 251,361 1

Hash (cost=23,973.26..23,973.26 rows=251,126 width=63) (actual time=13,238.562..13,238.562 rows=251,361 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 23,866kB
  • Buffers: shared hit=21,462
14. 11,985.748 11,985.748 ↓ 1.0 251,361 1

Seq Scan on tbl_ects_shipment_activated (cost=0.00..23,973.26 rows=251,126 width=63) (actual time=0.006..11,985.748 rows=251,361 loops=1)

  • Buffers: shared hit=21,462
Total runtime : 117,708.770 ms