explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mKeT : map query for alerts

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 71.152 ↓ 9.9 11,857 1

Recursive Union (cost=0.29..998.78 rows=1,201 width=62) (actual time=0.019..71.152 rows=11,857 loops=1)

2. 0.017 0.017 ↑ 1.0 1 1

Index Scan using org_pkey on org org_1 (cost=0.29..8.30 rows=1 width=62) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (org_id = '-1'::integer)
3. 5.017 65.989 ↓ 14.1 1,694 7

Nested Loop (cost=0.29..96.65 rows=120 width=62) (actual time=0.463..9.427 rows=1,694 loops=7)

4. 1.687 1.687 ↓ 169.4 1,694 7

WorkTable Scan on anon_3 anon_4 (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.241 rows=1,694 loops=7)

5. 59.285 59.285 ↑ 12.0 1 11,857

Index Scan using org_parent_id on org org_1_1 (cost=0.29..9.52 rows=12 width=62) (actual time=0.003..0.005 rows=1 loops=11,857)

  • Index Cond: (parent_id = anon_4.org_id)
6. 2,227.922 39,446.261 ↓ 3.6 6,999,477 1

Merge Join (cost=921,593.76..958,614.13 rows=1,955,168 width=226) (actual time=27,536.817..39,446.261 rows=6,999,477 loops=1)

  • Merge Cond: (org.org_id = device.org_id)
7. 12.634 1,186.024 ↑ 1.0 10,851 1

Merge Join (cost=3.61..1,448.24 rows=10,865 width=163) (actual time=1.545..1,186.024 rows=10,851 loops=1)

  • Merge Cond: (org.org_id = store.org_id)
8. 982.078 982.078 ↑ 1.0 11,842 1

Index Scan using org_pkey on org (cost=0.29..755.50 rows=11,857 width=62) (actual time=0.018..982.078 rows=11,842 loops=1)

9. 191.312 191.312 ↑ 1.0 10,851 1

Index Scan using store_pkey on store (cost=0.29..527.29 rows=10,865 width=101) (actual time=0.011..191.312 rows=10,851 loops=1)

10. 5,626.857 36,032.315 ↓ 3.3 6,999,477 1

Materialize (cost=921,584.45..932,252.85 rows=2,133,679 width=71) (actual time=27,534.234..36,032.315 rows=6,999,477 loops=1)

11. 23,673.975 30,405.458 ↓ 3.3 6,999,477 1

Sort (cost=921,584.45..926,918.65 rows=2,133,679 width=71) (actual time=27,534.226..30,405.458 rows=6,999,477 loops=1)

  • Sort Key: device.org_id
  • Sort Method: external merge Disk: 618872kB
12. 1,846.200 6,731.483 ↓ 3.3 6,999,477 1

Hash Join (cost=151,695.80..522,247.39 rows=2,133,679 width=71) (actual time=788.229..6,731.483 rows=6,999,477 loops=1)

  • Hash Cond: (alert_history.alert_enum = alert_type.alert_enum)
13. 2,166.055 4,885.140 ↓ 3.3 6,999,477 1

Hash Join (cost=151,691.15..492,904.65 rows=2,133,679 width=40) (actual time=788.044..4,885.140 rows=6,999,477 loops=1)

  • Hash Cond: (alert_history.im_con_id = device.im_con_id)
14. 1,942.026 2,606.983 ↑ 1.0 7,000,580 1

Bitmap Heap Scan on alert_history (cost=150,468.72..443,963.87 rows=7,035,422 width=29) (actual time=675.866..2,606.983 rows=7,000,580 loops=1)

  • Recheck Cond: ((datetime > '2018-11-27 00:00:00-06'::timestamp with time zone) AND (datetime < '2019-05-25 23:59:59-05'::timestamp with time zone))
  • Rows Removed by Index Recheck: 222
  • Filter: ((NOT deleted) AND alert_corp)
  • Rows Removed by Filter: 21025
  • Heap Blocks: exact=39201 lossy=26424
15. 664.957 664.957 ↑ 1.0 7,021,605 1

Bitmap Index Scan on alert_history_datetime (cost=0.00..148,709.87 rows=7,092,143 width=0) (actual time=664.957..664.957 rows=7,021,605 loops=1)

  • Index Cond: ((datetime > '2018-11-27 00:00:00-06'::timestamp with time zone) AND (datetime < '2019-05-25 23:59:59-05'::timestamp with time zone))
16. 4.839 112.102 ↓ 2.0 16,753 1

Hash (cost=1,117.73..1,117.73 rows=8,376 width=27) (actual time=112.102..112.102 rows=16,753 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1305kB
17. 5.568 107.263 ↓ 2.0 16,753 1

Hash Join (cost=31.52..1,117.73 rows=8,376 width=27) (actual time=90.831..107.263 rows=16,753 loops=1)

  • Hash Cond: (device.org_id = anon_3.org_id)
18. 11.676 11.676 ↑ 1.0 16,753 1

Seq Scan on device (cost=0.00..1,020.30 rows=16,753 width=23) (actual time=0.780..11.676 rows=16,753 loops=1)

  • Filter: ((org_id IS NOT NULL) AND ((nav_status)::text = ANY ('{Monitored,Not Monitored,Pending,Replaced}'::text[])))
  • Rows Removed by Filter: 10867
19. 2.009 90.019 ↓ 59.3 11,857 1

Hash (cost=29.02..29.02 rows=200 width=4) (actual time=90.019..90.019 rows=11,857 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 545kB
20. 8.758 88.010 ↓ 59.3 11,857 1

HashAggregate (cost=27.02..29.02 rows=200 width=4) (actual time=85.772..88.010 rows=11,857 loops=1)

  • Group Key: anon_3.org_id
21. 79.252 79.252 ↓ 9.9 11,857 1

CTE Scan on anon_3 (cost=0.00..24.02 rows=1,201 width=4) (actual time=0.024..79.252 rows=11,857 loops=1)

22. 0.057 0.143 ↑ 1.0 118 1

Hash (cost=3.18..3.18 rows=118 width=39) (actual time=0.143..0.143 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
23. 0.086 0.086 ↑ 1.0 118 1

Seq Scan on alert_type (cost=0.00..3.18 rows=118 width=39) (actual time=0.019..0.086 rows=118 loops=1)