explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lnS2 : alerts history data

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 23,354.910 ↑ 1.0 20 1

Limit (cost=774,521.91..774,521.96 rows=20 width=455) (actual time=23,354.896..23,354.910 rows=20 loops=1)

2.          

CTE anon_1

3. 3.481 25.268 ↓ 9.9 11,857 1

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

4. 0.010 0.010 ↑ 1.0 1 1

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

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

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

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

7. 11.857 11.857 ↑ 12.0 1 11,857

Index Scan using org_parent_id on org org_3_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_2.org_id)
8. 6,457.644 23,354.907 ↑ 106,103.2 20 1

Sort (cost=773,523.13..778,828.29 rows=2,122,064 width=455) (actual time=23,354.895..23,354.907 rows=20 loops=1)

  • Sort Key: alert_history.datetime DESC, alert_history.alert_id DESC
  • Sort Method: top-N heapsort Memory: 45kB
9. 2,710.894 16,897.263 ↓ 3.3 6,999,477 1

Hash Join (cost=155,060.48..717,055.77 rows=2,122,064 width=455) (actual time=799.638..16,897.263 rows=6,999,477 loops=1)

  • Hash Cond: (alert_history.alert_enum = alert_type.alert_enum)
10. 3,007.161 14,186.320 ↓ 3.3 6,999,477 1

Hash Join (cost=155,055.83..687,872.73 rows=2,122,064 width=455) (actual time=799.567..14,186.320 rows=6,999,477 loops=1)

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

Hash Left Join (cost=153,061.57..638,419.87 rows=6,997,126 width=455) (actual time=696.371..11,076.013 rows=7,000,580 loops=1)

  • Hash Cond: (alert_history.alert_enum = alert_type_1.alert_enum)
12. 5,506.474 7,892.935 ↓ 1.0 7,000,580 1

Hash Left Join (cost=153,056.91..542,204.73 rows=6,997,126 width=401) (actual time=696.270..7,892.935 rows=7,000,580 loops=1)

  • Hash Cond: (alert_history.im_con_id = device_1.im_con_id)
13. 1,708.766 2,289.258 ↓ 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=598.817..2,289.258 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
14. 580.492 580.492 ↑ 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=580.492..580.492 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))
15. 30.640 97.203 ↑ 1.0 27,620 1

Hash (cost=3,032.38..3,032.38 rows=27,620 width=362) (actual time=97.203..97.203 rows=27,620 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 11747kB
16. 14.952 66.563 ↑ 1.0 27,620 1

Hash Left Join (cost=1,413.74..3,032.38 rows=27,620 width=362) (actual time=24.158..66.563 rows=27,620 loops=1)

  • Hash Cond: (device_1.org_id = org_1.org_id)
17. 24.329 46.256 ↑ 1.0 27,620 1

Hash Left Join (cost=996.96..2,235.82 rows=27,620 width=300) (actual time=18.699..46.256 rows=27,620 loops=1)

  • Hash Cond: (device_1.org_id = org_2.org_id)
18. 3.345 3.345 ↑ 1.0 27,620 1

Seq Scan on device device_1 (cost=0.00..882.20 rows=27,620 width=137) (actual time=0.015..3.345 rows=27,620 loops=1)

19. 5.499 18.582 ↑ 1.0 10,865 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 2387kB
20. 5.773 13.083 ↑ 1.0 10,865 1

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

  • Hash Cond: (org_2.org_id = store_1.org_id)
21. 1.010 1.010 ↑ 1.0 11,857 1

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

22. 3.670 6.300 ↑ 1.0 10,865 1

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

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

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

24. 3.232 5.355 ↑ 1.0 11,857 1

Hash (cost=268.57..268.57 rows=11,857 width=62) (actual time=5.355..5.355 rows=11,857 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1275kB
25. 2.123 2.123 ↑ 1.0 11,857 1

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

26. 0.033 0.064 ↑ 1.0 118 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
27. 0.031 0.031 ↑ 1.0 118 1

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

28. 4.187 103.146 ↓ 2.0 16,753 1

Hash (cost=1,889.56..1,889.56 rows=8,376 width=8) (actual time=103.146..103.146 rows=16,753 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 911kB
29. 6.231 98.959 ↓ 2.0 16,753 1

Hash Join (cost=722.68..1,889.56 rows=8,376 width=8) (actual time=80.494..98.959 rows=16,753 loops=1)

  • Hash Cond: (device.org_id = store.org_id)
30. 12.470 12.470 ↑ 1.0 16,753 1

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

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

Hash (cost=654.78..654.78 rows=5,432 width=12) (actual time=80.258..80.258 rows=10,865 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 595kB
32. 5.428 76.895 ↓ 2.0 10,865 1

Hash Join (cost=287.42..654.78 rows=5,432 width=12) (actual time=70.239..76.895 rows=10,865 loops=1)

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

Seq Scan on org (cost=0.00..268.57 rows=11,857 width=4) (actual time=0.004..1.345 rows=11,857 loops=1)

34. 2.347 70.122 ↓ 2.0 10,865 1

Hash (cost=219.52..219.52 rows=5,432 width=8) (actual time=70.122..70.122 rows=10,865 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 553kB
35. 2.724 67.775 ↓ 2.0 10,865 1

Nested Loop (cost=27.31..219.52 rows=5,432 width=8) (actual time=39.114..67.775 rows=10,865 loops=1)

36. 9.443 41.337 ↓ 59.3 11,857 1

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

  • Group Key: anon_1.org_id
37. 31.894 31.894 ↓ 9.9 11,857 1

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

38. 23.714 23.714 ↑ 1.0 1 11,857

Index Only Scan using store_pkey on store (cost=0.29..0.94 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=11,857)

  • Index Cond: (org_id = anon_1.org_id)
  • Heap Fetches: 0
39. 0.019 0.049 ↑ 1.0 118 1

Hash (cost=3.18..3.18 rows=118 width=4) (actual time=0.049..0.049 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
40. 0.030 0.030 ↑ 1.0 118 1

Seq Scan on alert_type (cost=0.00..3.18 rows=118 width=4) (actual time=0.009..0.030 rows=118 loops=1)