explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DlkI : Optimization for: map query for alerts; plan #mKeT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 32.152 27,470.152 ↑ 2.6 3,809 1

Hash Join (cost=832,511.91..896,604.81 rows=9,851 width=247) (actual time=12,142.224..27,470.152 rows=3,809 loops=1)

  • Hash Cond: (device.org_id = store.org_id)
2.          

CTE anon_3

3. 3.587 26.438 ↓ 9.9 11,857 1

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

4. 0.010 0.010 ↑ 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.010..0.010 rows=1 loops=1)

  • Index Cond: (org_id = '-1'::integer)
5. 9.241 22.841 ↓ 14.1 1,694 7

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

6. 1.743 1.743 ↓ 169.4 1,694 7

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

7. 11.857 11.857 ↑ 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.001..0.001 rows=1 loops=11,857)

  • Index Cond: (parent_id = anon_4.org_id)
8. 11,575.749 27,409.012 ↑ 2.6 3,809 1

GroupAggregate (cost=830,516.17..894,325.85 rows=9,851 width=88) (actual time=12,112.853..27,409.012 rows=3,809 loops=1)

  • Group Key: device.org_id
9. 8,555.278 15,833.263 ↓ 3.3 6,999,477 1

Sort (cost=830,516.17..835,821.33 rows=2,122,064 width=67) (actual time=12,109.422..15,833.263 rows=6,999,477 loops=1)

  • Sort Key: device.org_id
  • Sort Method: external merge Disk: 610464kB
10. 1,834.491 7,277.985 ↓ 3.3 6,999,477 1

Hash Join (cost=150,906.36..520,480.68 rows=2,122,064 width=67) (actual time=959.110..7,277.985 rows=6,999,477 loops=1)

  • Hash Cond: (alert_history.alert_enum = alert_type.alert_enum)
11. 2,073.616 5,443.402 ↓ 3.3 6,999,477 1

Hash Join (cost=150,901.71..491,297.65 rows=2,122,064 width=36) (actual time=958.999..5,443.402 rows=6,999,477 loops=1)

  • Hash Cond: (alert_history.im_con_id = device.im_con_id)
12. 2,428.895 3,284.561 ↓ 1.0 7,000,580 1

Bitmap Heap Scan on alert_history (cost=149,679.28..442,616.62 rows=6,997,126 width=29) (actual time=873.662..3,284.561 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))
  • Filter: ((NOT deleted) AND alert_corp)
  • Rows Removed by Filter: 21025
  • Heap Blocks: exact=65625
13. 855.666 855.666 ↑ 1.0 7,021,605 1

Bitmap Index Scan on alert_history_datetime (cost=0.00..147,930.00 rows=7,054,956 width=0) (actual time=855.666..855.666 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))
14. 6.808 85.225 ↓ 2.0 16,753 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1225kB
15. 8.457 78.417 ↓ 2.0 16,753 1

Hash Join (cost=31.52..1,117.73 rows=8,376 width=23) (actual time=46.336..78.417 rows=16,753 loops=1)

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

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

  • Filter: ((nav_status)::text = ANY ('{Monitored,""Not Monitored"",Pending,Replaced}'::text[]))
  • Rows Removed by Filter: 10867
17. 2.247 46.041 ↓ 59.3 11,857 1

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

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 545kB
18. 7.619 43.794 ↓ 59.3 11,857 1

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

  • Group Key: anon_3.org_id
19. 36.175 36.175 ↓ 9.9 11,857 1

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

20. 0.035 0.092 ↑ 1.0 118 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
21. 0.057 0.057 ↑ 1.0 118 1

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

22. 7.045 28.988 ↑ 1.0 10,865 1

Hash (cost=861.15..861.15 rows=10,865 width=163) (actual time=28.988..28.988 rows=10,865 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2410kB
23. 9.701 21.943 ↑ 1.0 10,865 1

Hash Join (cost=439.46..861.15 rows=10,865 width=163) (actual time=10.925..21.943 rows=10,865 loops=1)

  • Hash Cond: (org.org_id = store.org_id)
24. 1.731 1.731 ↑ 1.0 11,857 1

Seq Scan on org (cost=0.00..268.57 rows=11,857 width=62) (actual time=0.011..1.731 rows=11,857 loops=1)

25. 4.349 10.511 ↑ 1.0 10,865 1

Hash (cost=303.65..303.65 rows=10,865 width=101) (actual time=10.511..10.511 rows=10,865 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1670kB
26. 6.162 6.162 ↑ 1.0 10,865 1

Seq Scan on store (cost=0.00..303.65 rows=10,865 width=101) (actual time=0.007..6.162 rows=10,865 loops=1)