explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V8w1 : Optimization for: alerts history data; plan #lnS2

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 25,168.086 ↑ 1.0 20 1

Limit (cost=578,883.25..578,883.30 rows=20 width=393) (actual time=25,168.078..25,168.086 rows=20 loops=1)

2.          

CTE anon_1

3. 12.096 58.030 ↓ 9.9 11,857 1

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

4. 0.014 0.014 ↑ 1.0 1 1

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

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

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

6. 1.694 1.694 ↓ 169.4 1,694 7

WorkTable Scan on anon_1 anon_2 (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.242 rows=1,694 loops=7)

7. 35.571 35.571 ↑ 12.0 1 11,857

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

  • Index Cond: (parent_id = anon_2.org_id)
8. 6,998.913 25,168.082 ↑ 106,103.2 20 1

Sort (cost=577,884.47..583,189.63 rows=2,122,064 width=393) (actual time=25,168.076..25,168.082 rows=20 loops=1)

  • Sort Key: alert_history.datetime DESC, alert_history.alert_id DESC
  • Sort Method: top-N heapsort Memory: 43kB
9. 3,520.134 18,169.169 ↓ 3.3 6,999,477 1

Hash Join (cost=151,842.79..521,417.11 rows=2,122,064 width=393) (actual time=2,106.923..18,169.169 rows=6,999,477 loops=1)

  • Hash Cond: (alert_history.alert_enum = alert_type_1.alert_enum)
10. 5,850.016 14,645.287 ↓ 3.3 6,999,477 1

Hash Join (cost=151,838.13..492,234.08 rows=2,122,064 width=339) (actual time=2,103.114..14,645.287 rows=6,999,477 loops=1)

  • Hash Cond: (alert_history.im_con_id = device.im_con_id)
11. 6,716.459 8,515.329 ↓ 1.0 7,000,580 1

Bitmap Heap Scan on alert_history (cost=149,679.28..442,616.62 rows=6,997,126 width=39) (actual time=1,822.914..8,515.329 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. 1,798.870 1,798.870 ↑ 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=1,798.870..1,798.870 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. 30.007 279.942 ↓ 2.0 16,753 1

Hash (cost=2,054.15..2,054.15 rows=8,376 width=300) (actual time=279.942..279.942 rows=16,753 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 6688kB
14. 14.327 249.935 ↓ 2.0 16,753 1

Hash Join (cost=887.27..2,054.15 rows=8,376 width=300) (actual time=148.648..249.935 rows=16,753 loops=1)

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

Seq Scan on device (cost=0.00..1,020.30 rows=16,753 width=137) (actual time=0.513..87.702 rows=16,753 loops=1)

  • Filter: ((nav_status)::text = ANY ('{Monitored,""Not Monitored"",Pending,Replaced}'::text[]))
  • Rows Removed by Filter: 10867
16. 10.133 147.906 ↓ 2.0 10,865 1

Hash (cost=819.37..819.37 rows=5,432 width=167) (actual time=147.906..147.906 rows=10,865 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2484kB
17. 20.229 137.773 ↓ 2.0 10,865 1

Hash Join (cost=420.66..819.37 rows=5,432 width=167) (actual time=113.561..137.773 rows=10,865 loops=1)

  • Hash Cond: (store_1.org_id = org_1.org_id)
18. 4.215 4.215 ↑ 1.0 10,865 1

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

19. 4.702 113.329 ↓ 2.0 11,857 1

Hash (cost=346.56..346.56 rows=5,928 width=66) (actual time=113.329..113.329 rows=11,857 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1356kB
20. 5.704 108.627 ↓ 2.0 11,857 1

Hash Join (cost=31.52..346.56 rows=5,928 width=66) (actual time=101.893..108.627 rows=11,857 loops=1)

  • Hash Cond: (org_1.org_id = anon_1.org_id)
21. 1.092 1.092 ↑ 1.0 11,857 1

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

22. 1.890 101.831 ↓ 59.3 11,857 1

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

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 545kB
23. 11.365 99.941 ↓ 59.3 11,857 1

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

  • Group Key: anon_1.org_id
24. 88.576 88.576 ↓ 9.9 11,857 1

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

25. 3.600 3.748 ↑ 1.0 118 1

Hash (cost=3.18..3.18 rows=118 width=54) (actual time=3.748..3.748 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
26. 0.148 0.148 ↑ 1.0 118 1

Seq Scan on alert_type alert_type_1 (cost=0.00..3.18 rows=118 width=54) (actual time=0.033..0.148 rows=118 loops=1)

Planning time : 59.583 ms
Execution time : 25,178.870 ms