explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 16,917.143 ↑ 1.0 20 1

Limit (cost=549,700.22..549,700.27 rows=20 width=339) (actual time=16,917.140..16,917.143 rows=20 loops=1)

2.          

CTE anon_1

3. 4.053 33.471 ↓ 9.9 11,857 1

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

4. 0.025 0.025 ↑ 1.0 1 1

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

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

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

6. 1.715 1.715 ↓ 169.4 1,694 7

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

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

  • Index Cond: (parent_id = anon_2.org_id)
8. 6,129.191 16,917.141 ↑ 106,103.2 20 1

Sort (cost=548,701.44..554,006.60 rows=2,122,064 width=339) (actual time=16,917.139..16,917.141 rows=20 loops=1)

  • Sort Key: alert_history.datetime DESC, alert_history.alert_id DESC
  • Sort Method: top-N heapsort Memory: 35kB
9. 6,832.018 10,787.950 ↓ 3.3 6,999,477 1

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

  • Hash Cond: (alert_history.im_con_id = device.im_con_id)
10. 2,819.889 3,770.498 ↓ 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=983.916..3,770.498 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
11. 950.609 950.609 ↑ 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=950.609..950.609 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))
12. 19.558 185.434 ↓ 2.0 16,753 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 6688kB
13. 12.237 165.876 ↓ 2.0 16,753 1

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

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

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

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

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

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2484kB
16. 14.503 85.829 ↓ 2.0 10,865 1

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

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

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

18. 4.726 69.449 ↓ 2.0 11,857 1

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

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1356kB
19. 5.939 64.723 ↓ 2.0 11,857 1

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

  • Hash Cond: (org_1.org_id = anon_1.org_id)
20. 1.283 1.283 ↑ 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.283 rows=11,857 loops=1)

21. 1.942 57.501 ↓ 59.3 11,857 1

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

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 545kB
22. 9.295 55.559 ↓ 59.3 11,857 1

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

  • Group Key: anon_1.org_id
23. 46.264 46.264 ↓ 9.9 11,857 1

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