explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0ARX : Optimization for: map query for alerts; plan #mKeT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.275 11,500.917 ↑ 2.6 3,809 1

Hash Join (cost=570,222.86..570,555.33 rows=9,851 width=183) (actual time=11,496.346..11,500.917 rows=3,809 loops=1)

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

CTE anon_3

3. 4.667 32.519 ↓ 9.9 11,857 1

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

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

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

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

6. 2.303 2.303 ↓ 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.329 rows=1,694 loops=7)

7. 23.714 23.714 ↑ 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.002 rows=1 loops=11,857)

  • Index Cond: (parent_id = anon_4.org_id)
8. 3,848.742 11,465.204 ↑ 2.6 3,809 1

HashAggregate (cost=568,227.12..568,325.63 rows=9,851 width=24) (actual time=11,463.620..11,465.204 rows=3,809 loops=1)

  • Group Key: device.org_id
9. 1,879.271 7,616.462 ↓ 3.3 6,999,477 1

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

  • Hash Cond: (alert_history.alert_enum = alert_type.alert_enum)
10. 2,178.099 5,737.051 ↓ 3.3 6,999,477 1

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

  • Hash Cond: (alert_history.im_con_id = device.im_con_id)
11. 2,598.897 3,469.554 ↓ 1.0 7,000,580 1

Bitmap Heap Scan on alert_history (cost=149,679.28..442,616.62 rows=6,997,126 width=21) (actual time=889.446..3,469.554 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
12. 870.657 870.657 ↑ 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=870.657..870.657 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))
13. 5.046 89.398 ↓ 2.0 16,753 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1042kB
14. 7.373 84.352 ↓ 2.0 16,753 1

Hash Join (cost=31.52..1,117.73 rows=8,376 width=12) (actual time=57.542..84.352 rows=16,753 loops=1)

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

Seq Scan on device (cost=0.00..1,020.30 rows=16,753 width=12) (actual time=0.666..20.134 rows=16,753 loops=1)

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

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

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 545kB
17. 9.050 54.524 ↓ 59.3 11,857 1

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

  • Group Key: anon_3.org_id
18. 45.474 45.474 ↓ 9.9 11,857 1

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

19. 0.063 0.140 ↑ 1.0 118 1

Hash (cost=3.18..3.18 rows=118 width=19) (actual time=0.140..0.140 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
20. 0.077 0.077 ↑ 1.0 118 1

Seq Scan on alert_type (cost=0.00..3.18 rows=118 width=19) (actual time=0.015..0.077 rows=118 loops=1)

21. 7.608 32.438 ↑ 1.0 10,865 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 2410kB
22. 10.842 24.830 ↑ 1.0 10,865 1

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

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

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

24. 4.977 11.970 ↑ 1.0 10,865 1

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

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

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