explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ed6P : with index

Settings
# exclusive inclusive rows x rows loops node
1. 13.299 62,511.585 ↑ 42.4 21,654 1

HashAggregate (cost=3,102,974.37..3,112,158.22 rows=918,385 width=58) (actual time=62,507.747..62,511.585 rows=21,654 loops=1)

2. 1.485 62,498.286 ↑ 42.4 21,654 1

Append (cost=0.00..3,082,310.71 rows=918,385 width=58) (actual time=62,488.721..62,498.286 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. 19.973 62,496.800 ↑ 42.4 21,654 1

HashAggregate (cost=1,854,491.75..1,863,675.59 rows=918,384 width=58) (actual time=62,488.720..62,496.800 rows=21,654 loops=1)

6.          

CTE from_phish_report

7. 0.013 0.066 ↑ 25.1 14 1

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

8. 0.003 0.053 ↑ 25.1 14 1

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

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

  • Index Cond: (investigation_id = 115,248)
  • Heap Fetches: 0
10. 0.033 0.033 ↑ 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.008..0.011 rows=5 loops=3)

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

CTE from_investigation_message

12. 17.553 9,097.098 ↑ 42.4 21,650 1

HashAggregate (cost=168,173.04..177,353.36 rows=918,032 width=8) (actual time=9,089.595..9,097.098 rows=21,650 loops=1)

13. 8.545 9,079.545 ↑ 40.5 22,645 1

Nested Loop (cost=0.86..165,877.96 rows=918,032 width=8) (actual time=0.724..9,079.545 rows=22,645 loops=1)

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

  • Index Cond: (investigation_id = 115,248)
15. 9,066.515 9,066.515 ↑ 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.063..1.805 rows=5 loops=5,023)

  • Index Cond: (forensics_id = investigation_message.forensics_id)
16. 1.660 62,476.827 ↑ 42.4 21,664 1

Append (cost=0.56..1,656,332.65 rows=918,384 width=58) (actual time=0.074..62,476.827 rows=21,664 loops=1)

17. 0.007 0.159 ↑ 25.1 14 1

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

18. 0.068 0.068 ↑ 25.1 14 1

CTE Scan on from_phish_report (cost=0.00..7.04 rows=352 width=8) (actual time=0.061..0.068 rows=14 loops=1)

19. 0.084 0.084 ↑ 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.006..0.006 rows=1 loops=14)

  • Index Cond: (id = from_phish_report.id)
20. 1,141.498 62,475.008 ↑ 42.4 21,650 1

Hash Join (cost=1,474,223.11..1,646,230.09 rows=918,032 width=58) (actual time=61,328.665..62,475.008 rows=21,650 loops=1)

  • Hash Cond: (from_investigation_message.id = ip_investigation_2.id)
21. 9,102.164 9,102.164 ↑ 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,089.598..9,102.164 rows=21,650 loops=1)

22. 2,892.641 52,231.346 ↑ 1.0 11,310,464 1

Hash (cost=1,209,451.27..1,209,451.27 rows=11,391,827 width=58) (actual time=52,231.346..52,231.346 rows=11,310,464 loops=1)

  • Buckets: 2,097,152 Batches: 2 Memory Usage: 510,906kB
23. 49,338.705 49,338.705 ↑ 1.0 11,310,464 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.509..49,338.705 rows=11,310,464 loops=1)

Total runtime : 62,520.842 ms