explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SsGp

Settings
# exclusive inclusive rows x rows loops node
1. 17.913 98,031.092 ↑ 113.8 21,654 1

HashAggregate (cost=4,325,005.17..4,349,639.11 rows=2,463,394 width=58) (actual time=98,023.571..98,031.092 rows=21,654 loops=1)

2. 1.546 98,013.179 ↑ 113.8 21,654 1

Append (cost=0.00..4,269,578.81 rows=2,463,394 width=58) (actual time=97,998.860..98,013.179 rows=21,654 loops=1)

3. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..1,401,097.32 rows=1 width=58) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
4. 0.000 0.000 ↓ 0.0 0

Seq Scan on ip_investigation (cost=0.00..1,401,097.32 rows=1 width=58) (never executed)

5. 35.666 98,011.633 ↑ 113.8 21,654 1

HashAggregate (cost=2,819,213.62..2,843,847.55 rows=2,463,393 width=58) (actual time=97,998.859..98,011.633 rows=21,654 loops=1)

6.          

CTE from_phish_report

7. 0.011 0.057 ↑ 67.5 14 1

HashAggregate (cost=274.04..283.49 rows=945 width=8) (actual time=0.054..0.057 rows=14 loops=1)

8. 0.003 0.046 ↑ 67.5 14 1

Nested Loop (cost=0.86..271.68 rows=945 width=8) (actual time=0.027..0.046 rows=14 loops=1)

9. 0.016 0.016 ↑ 1.3 3 1

Index Only Scan using index_phish_report_on_investigation_id_and_forensics_id on phish_report (cost=0.42..2.49 rows=4 width=8) (actual time=0.015..0.016 rows=3 loops=1)

  • Index Cond: (investigation_id = 115248)
  • Heap Fetches: 0
10. 0.027 0.027 ↑ 69.6 5 3

Index Scan using _mfrisch_index_ip_investigation_on_forensics_id on ip_investigation ip_investigation_3 (cost=0.44..63.82 rows=348 width=12) (actual time=0.007..0.009 rows=5 loops=3)

  • Index Cond: (forensics_id = phish_report.forensics_id)
11.          

CTE from_investigation_message

12. 24.471 8,744.743 ↑ 113.7 21,650 1

HashAggregate (cost=268,999.44..293,623.92 rows=2,462,448 width=8) (actual time=8,730.868..8,744.743 rows=21,650 loops=1)

13. 6.253 8,720.272 ↑ 108.7 22,645 1

Nested Loop (cost=0.87..262,843.32 rows=2,462,448 width=8) (actual time=1.590..8,720.272 rows=22,645 loops=1)

14. 4.137 4.137 ↑ 1.1 5,023 1

Index Scan using _nb_index_investigation_message_on_investigation_id on investigation_message (cost=0.43..550.81 rows=5,638 width=8) (actual time=0.018..4.137 rows=5,023 loops=1)

  • Index Cond: (investigation_id = 115248)
15. 8,709.882 8,709.882 ↑ 69.6 5 5,023

Index Scan using _mfrisch_index_ip_investigation_on_forensics_id on ip_investigation ip_investigation_4 (cost=0.44..43.04 rows=348 width=12) (actual time=0.055..1.734 rows=5 loops=5,023)

  • Index Cond: (forensics_id = investigation_message.forensics_id)
16. 2.759 97,975.967 ↑ 113.7 21,664 1

Append (cost=0.56..2,469,879.86 rows=2,463,393 width=58) (actual time=0.068..97,975.967 rows=21,664 loops=1)

17. 0.010 0.111 ↑ 67.5 14 1

Nested Loop (cost=0.56..2,468.81 rows=945 width=58) (actual time=0.067..0.111 rows=14 loops=1)

18. 0.059 0.059 ↑ 67.5 14 1

CTE Scan on from_phish_report (cost=0.00..18.90 rows=945 width=8) (actual time=0.055..0.059 rows=14 loops=1)

19. 0.042 0.042 ↑ 1.0 1 14

Index Scan using ip_investigation_pkey on ip_investigation ip_investigation_1 (cost=0.56..2.58 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=14)

  • Index Cond: (id = from_phish_report.id)
20. 948.713 97,973.097 ↑ 113.7 21,650 1

Merge Join (cost=310,658.84..2,442,777.12 rows=2,462,448 width=58) (actual time=54,367.693..97,973.097 rows=21,650 loops=1)

  • Merge Cond: (ip_investigation_2.id = from_investigation_message.id)
21. 88,261.400 88,261.400 ↑ 2.7 11,301,347 1

Index Scan using ip_investigation_pkey on ip_investigation ip_investigation_2 (cost=0.56..2,018,791.04 rows=30,556,432 width=58) (actual time=28.197..88,261.400 rows=11,301,347 loops=1)

22. 12.087 8,762.984 ↑ 113.7 21,650 1

Sort (cost=310,658.28..316,814.40 rows=2,462,448 width=8) (actual time=8,757.734..8,762.984 rows=21,650 loops=1)

  • Sort Key: from_investigation_message.id
  • Sort Method: quicksort Memory: 1783kB
23. 8,750.897 8,750.897 ↑ 113.7 21,650 1

CTE Scan on from_investigation_message (cost=0.00..49,248.96 rows=2,462,448 width=8) (actual time=8,730.871..8,750.897 rows=21,650 loops=1)

Total runtime : 98,076.037 ms