explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W6V17

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 3,264.513 ↑ 6,915.3 135 1

Hash Left Join (cost=325,947.21..525,797.75 rows=933,567 width=89) (actual time=3,263.398..3,264.513 rows=135 loops=1)

  • Hash Cond: (ndalarmhistory_v2.ndalarm_id = ndalarmtext.ndalarm_id)
2. 0.079 673.457 ↑ 6,915.3 135 1

Nested Loop (cost=117,851.14..304,865.14 rows=933,567 width=64) (actual time=672.368..673.457 rows=135 loops=1)

3. 157.714 672.433 ↑ 296.3 135 1

HashAggregate (cost=117,851.14..118,251.14 rows=40,000 width=20) (actual time=672.323..672.433 rows=135 loops=1)

  • Group Key: ndalarmhistory_v2_1.ndalarm_id, ndalarmhistory_v2_1.action
4. 33.922 514.719 ↓ 1.0 954,335 1

Append (cost=0.00..110,849.39 rows=933,567 width=20) (actual time=133.956..514.719 rows=954,335 loops=1)

5. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on ndalarmhistory_v2 ndalarmhistory_v2_1 (cost=0.00..0.00 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[]))
6. 355.562 480.796 ↓ 1.0 954,335 1

Bitmap Heap Scan on ndalarmhistory_v2_0 ndalarmhistory_v2_0_1 (cost=18,966.88..110,849.39 rows=933,566 width=20) (actual time=133.954..480.796 rows=954,335 loops=1)

  • Recheck Cond: (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[]))
  • Heap Blocks: exact=50539
7. 125.234 125.234 ↓ 1.0 954,335 1

Bitmap Index Scan on ndalarmhistory_v2_0_ndalarm_id_fkey_ix (cost=0.00..18,733.48 rows=933,566 width=0) (actual time=125.234..125.234 rows=954,335 loops=1)

  • Index Cond: (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[]))
8. 0.540 0.945 ↑ 2.0 1 135

Append (cost=0.00..4.64 rows=2 width=90) (actual time=0.007..0.007 rows=1 loops=135)

9. 0.000 0.000 ↓ 0.0 0 135

Seq Scan on ndalarmhistory_v2 (cost=0.00..0.00 rows=1 width=116) (actual time=0.000..0.000 rows=0 loops=135)

  • Filter: ((ndalarmhistory_v2_1.action = action) AND ((max(ndalarmhistory_v2_1.actiontime)) = actiontime) AND (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[])))
10. 0.405 0.405 ↑ 1.0 1 135

Index Scan using ndalarmhistory_v2_0_actionime_ix on ndalarmhistory_v2_0 (cost=0.43..4.64 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=135)

  • Index Cond: (actiontime = (max(ndalarmhistory_v2_1.actiontime)))
  • Filter: ((ndalarmhistory_v2_1.action = action) AND (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[])))
11. 0.037 2,591.016 ↑ 7.4 27 1

Hash (cost=208,093.57..208,093.57 rows=200 width=41) (actual time=2,591.016..2,591.016 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
12. 0.019 2,590.979 ↑ 7.4 27 1

Subquery Scan on ndalarmtext (cost=203,423.73..208,093.57 rows=200 width=41) (actual time=2,321.922..2,590.979 rows=27 loops=1)

13. 41.436 2,590.960 ↑ 7.4 27 1

Unique (cost=203,423.73..208,091.57 rows=200 width=49) (actual time=2,321.921..2,590.960 rows=27 loops=1)

14. 2,265.892 2,549.524 ↓ 1.0 954,335 1

Sort (cost=203,423.73..205,757.65 rows=933,567 width=49) (actual time=2,321.920..2,549.524 rows=954,335 loops=1)

  • Sort Key: ndalarmhistory_v2_2.ndalarm_id, ndalarmhistory_v2_2.actiontime
  • Sort Method: external merge Disk: 61544kB
15. 33.422 283.632 ↓ 1.0 954,335 1

Append (cost=0.00..110,849.39 rows=933,567 width=49) (actual time=70.656..283.632 rows=954,335 loops=1)

16. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on ndalarmhistory_v2 ndalarmhistory_v2_2 (cost=0.00..0.00 rows=1 width=48) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[]))
17. 186.183 250.210 ↓ 1.0 954,335 1

Bitmap Heap Scan on ndalarmhistory_v2_0 ndalarmhistory_v2_0_2 (cost=18,966.88..110,849.39 rows=933,566 width=49) (actual time=70.655..250.210 rows=954,335 loops=1)

  • Recheck Cond: (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[]))
  • Heap Blocks: exact=50539
18. 64.027 64.027 ↓ 1.0 954,335 1

Bitmap Index Scan on ndalarmhistory_v2_0_ndalarm_id_fkey_ix (cost=0.00..18,733.48 rows=933,566 width=0) (actual time=64.027..64.027 rows=954,335 loops=1)

  • Index Cond: (ndalarm_id = ANY ('{12,79,10,42,90,18,59,78,98,100,63,9,24,64,55,68,84,88,38,74,6,71,29,2,72,41,7}'::bigint[]))
Planning time : 8.373 ms
Execution time : 3,284.331 ms