explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JBYT : new index 2

Settings
# exclusive inclusive rows x rows loops node
1. 14.000 23,733.772 ↑ 42.4 21,654 1

HashAggregate (cost=3,102,975.37..3,112,159.22 rows=918,385 width=58) (actual time=23,729.764..23,733.772 rows=21,654 loops=1)

2. 1.507 23,719.772 ↑ 42.4 21,654 1

Append (cost=0.00..3,082,311.71 rows=918,385 width=58) (actual time=23,709.718..23,719.772 rows=21,654 loops=1)

3. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..1,209,451.27 rows=1 width=58) (actual time=0.001..0.001 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,209,451.27 rows=1 width=58) (never executed)

5. 20.173 23,718.264 ↑ 42.4 21,654 1

HashAggregate (cost=1,854,492.75..1,863,676.59 rows=918,384 width=58) (actual time=23,709.717..23,718.264 rows=21,654 loops=1)

6.          

CTE from_phish_report

7. 0.009 0.055 ↑ 25.1 14 1

HashAggregate (cost=138.58..142.10 rows=352 width=8) (actual time=0.051..0.055 rows=14 loops=1)

8. 0.005 0.046 ↑ 25.1 14 1

Nested Loop (cost=0.85..137.70 rows=352 width=8) (actual time=0.025..0.046 rows=14 loops=1)

9. 0.014 0.014 ↑ 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.014..0.014 rows=3 loops=1)

  • Index Cond: (investigation_id = 115,248)
  • Heap Fetches: 0
10. 0.027 0.027 ↑ 26.0 5 3

Index Scan using _mfrisch_index_ip_investigation_on_forensics_id on ip_investigation ip_investigation_3 (cost=0.43..32.50 rows=130 width=12) (actual time=0.006..0.009 rows=5 loops=3)

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

CTE from_investigation_message

12. 16.596 9,155.035 ↑ 42.4 21,650 1

HashAggregate (cost=168,174.04..177,354.36 rows=918,032 width=8) (actual time=9,148.169..9,155.035 rows=21,650 loops=1)

13. 7.310 9,138.439 ↑ 40.5 22,645 1

Nested Loop (cost=0.86..165,878.96 rows=918,032 width=8) (actual time=0.595..9,138.439 rows=22,645 loops=1)

14. 4.338 4.338 ↑ 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.017..4.338 rows=5,023 loops=1)

  • Index Cond: (investigation_id = 115,248)
15. 9,126.791 9,126.791 ↑ 26.0 5 5,023

Index Scan using _mfrisch_index_ip_investigation_on_forensics_id on ip_investigation ip_investigation_4 (cost=0.43..28.02 rows=130 width=12) (actual time=0.060..1.817 rows=5 loops=5,023)

  • Index Cond: (forensics_id = investigation_message.forensics_id)
16. 1.721 23,698.091 ↑ 42.4 21,664 1

Append (cost=0.56..1,656,332.65 rows=918,384 width=58) (actual time=0.062..23,698.091 rows=21,664 loops=1)

17. 0.006 0.108 ↑ 25.1 14 1

Nested Loop (cost=0.56..918.72 rows=352 width=58) (actual time=0.062..0.108 rows=14 loops=1)

18. 0.060 0.060 ↑ 25.1 14 1

CTE Scan on from_phish_report (cost=0.00..7.04 rows=352 width=8) (actual time=0.051..0.060 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. 1,117.207 23,696.262 ↑ 42.4 21,650 1

Hash Join (cost=1,474,223.11..1,646,230.09 rows=918,032 width=58) (actual time=22,569.050..23,696.262 rows=21,650 loops=1)

  • Hash Cond: (from_investigation_message.id = ip_investigation_2.id)
21. 9,159.957 9,159.957 ↑ 42.4 21,650 1

CTE Scan on from_investigation_message (cost=0.00..18,360.64 rows=918,032 width=8) (actual time=9,148.172..9,159.957 rows=21,650 loops=1)

22. 2,352.334 13,419.098 ↑ 1.0 11,310,521 1

Hash (cost=1,209,451.27..1,209,451.27 rows=11,391,827 width=58) (actual time=13,419.098..13,419.098 rows=11,310,521 loops=1)

  • Buckets: 2,097,152 Batches: 2 Memory Usage: 510,908kB
23. 11,066.764 11,066.764 ↑ 1.0 11,310,521 1

Seq Scan on ip_investigation ip_investigation_2 (cost=0.00..1,209,451.27 rows=11,391,827 width=58) (actual time=0.019..11,066.764 rows=11,310,521 loops=1)

Total runtime : 23,739.640 ms