explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S3Se

Settings
# exclusive inclusive rows x rows loops node
1. 76.912 177,252.650 ↑ 559.2 66 1

HashAggregate (cost=2,359,269.46..2,359,638.55 rows=36,909 width=32) (actual time=177,197.635..177,252.650 rows=66 loops=1)

  • Group Key: threat_hashes.hash
2.          

CTE existing_hashes

3. 26.094 33.633 ↑ 1.0 27,057 1

HashAggregate (cost=618.35..889.00 rows=27,065 width=5) (actual time=24.657..33.633 rows=27,057 loops=1)

  • Group Key: threat_hashes_long.short_hash
4. 7.539 7.539 ↑ 1.0 27,060 1

Seq Scan on threat_hashes_long (cost=0.00..550.68 rows=27,068 width=5) (actual time=0.011..7.539 rows=27,060 loops=1)

5. 3.075 177,175.738 ↑ 559.2 66 1

Append (cost=47,603.21..2,358,288.19 rows=36,909 width=32) (actual time=1,391.179..177,175.738 rows=66 loops=1)

6. 0.025 1,403.413 ↑ 962.9 38 1

Unique (cost=47,603.21..48,212.53 rows=36,589 width=5) (actual time=1,391.177..1,403.413 rows=38 loops=1)

7. 6.393 1,403.388 ↑ 871.2 42 1

Merge Anti Join (cost=47,603.21..48,121.06 rows=36,589 width=5) (actual time=1,391.176..1,403.388 rows=42 loops=1)

  • Merge Cond: (threat_hashes.hash = existing_hashes.short_hash)
8. 0.000 1,317.686 ↑ 1,663.1 44 1

Sort (cost=45,069.36..45,252.31 rows=73,178 width=5) (actual time=1,317.675..1,317.686 rows=44 loops=1)

  • Sort Key: threat_hashes.hash
  • Sort Method: quicksort Memory: 27kB
9. 14.168 1,323.803 ↑ 1,663.1 44 1

Gather (cost=26,184.44..39,156.90 rows=73,178 width=5) (actual time=1,189.362..1,323.803 rows=44 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 383.230 1,309.635 ↑ 2,032.7 15 3 / 3

Parallel Hash Join (cost=25,184.44..30,839.10 rows=30,491 width=5) (actual time=1,164.852..1,309.635 rows=15 loops=3)

  • Hash Cond: ("substring"(link_safe_hash.urlhash, 1, 4) = threat_hashes.hash)
11. 160.623 160.623 ↓ 5.2 151,918 3 / 3

Parallel Index Scan using idx__linkid_1 on link_safe_hash (cost=0.57..2,909.31 rows=29,375 width=33) (actual time=0.031..160.623 rows=151,918 loops=3)

  • Index Cond: ((linkid >= 292361632) AND (linkid <= 292411632))
12. 411.603 765.782 ↑ 1.2 412,568 3 / 3

Parallel Hash (cost=16,735.83..16,735.83 rows=514,883 width=5) (actual time=765.781..765.782 rows=412,568 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 4096kB
13. 354.179 354.179 ↑ 1.2 412,568 3 / 3

Parallel Seq Scan on threat_hashes (cost=0.00..16,735.83 rows=514,883 width=5) (actual time=0.009..354.179 rows=412,568 loops=3)

14. 30.697 79.309 ↑ 1.0 26,517 1

Sort (cost=2,533.84..2,601.51 rows=27,065 width=32) (actual time=73.085..79.309 rows=26,517 loops=1)

  • Sort Key: existing_hashes.short_hash
  • Sort Method: quicksort Memory: 2037kB
15. 48.612 48.612 ↑ 1.0 27,057 1

CTE Scan on existing_hashes (cost=0.00..541.30 rows=27,065 width=32) (actual time=24.661..48.612 rows=27,057 loops=1)

16. 0.453 175,769.250 ↑ 11.4 28 1

Unique (cost=2,309,130.17..2,309,522.02 rows=320 width=5) (actual time=175,756.890..175,769.250 rows=28 loops=1)

17. 8.785 175,768.797 ↑ 495.1 40 1

Merge Anti Join (cost=2,309,130.17..2,309,472.52 rows=19,802 width=5) (actual time=175,756.883..175,768.797 rows=40 loops=1)

  • Merge Cond: (threat_hashes_1.hash = existing_hashes_1.short_hash)
18. 0.579 175,551.335 ↑ 990.1 40 1

Sort (cost=2,306,596.33..2,306,695.34 rows=39,604 width=5) (actual time=175,551.317..175,551.335 rows=40 loops=1)

  • Sort Key: threat_hashes_1.hash
  • Sort Method: quicksort Memory: 26kB
19. 34.681 175,550.756 ↑ 990.1 40 1

Gather (cost=1,580.00..2,303,571.90 rows=39,604 width=5) (actual time=13,753.273..175,550.756 rows=40 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 19,803.644 175,516.075 ↑ 1,269.4 13 3 / 3

Hash Join (cost=580.00..2,298,611.50 rows=16,502 width=5) (actual time=14,698.556..175,516.075 rows=13 loops=3)

  • Hash Cond: ("substring"(link_safe_hash_1.urlhash, 1, 4) = threat_hashes_1.hash)
21. 155,709.174 155,709.174 ↑ 1.3 49,112,856 3 / 3

Parallel Seq Scan on link_safe_hash link_safe_hash_1 (cost=0.00..1,990,910.65 rows=61,391,165 width=33) (actual time=0.026..155,709.174 rows=49,112,856 loops=3)

22. 1.998 3.257 ↓ 9.9 3,173 3 / 3

Hash (cost=576.00..576.00 rows=320 width=5) (actual time=3.257..3.257 rows=3,173 loops=3)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 147kB
23. 1.259 1.259 ↓ 9.9 3,173 3 / 3

Index Scan using threat_hashes_pkey on threat_hashes threat_hashes_1 (cost=0.43..576.00 rows=320 width=5) (actual time=0.030..1.259 rows=3,173 loops=3)

  • Index Cond: ((threattype = 'SOCIAL_ENGINEERING'::threattypeenum) AND (idx >= 1613287) AND (idx <= 1616459))
24. 71.638 208.677 ↑ 1.1 23,905 1

Sort (cost=2,533.84..2,601.51 rows=27,065 width=32) (actual time=202.403..208.677 rows=23,905 loops=1)

  • Sort Key: existing_hashes_1.short_hash
  • Sort Method: quicksort Memory: 2037kB
25. 137.039 137.039 ↑ 1.0 27,057 1

CTE Scan on existing_hashes existing_hashes_1 (cost=0.00..541.30 rows=27,065 width=32) (actual time=1.435..137.039 rows=27,057 loops=1)

Planning time : 0.514 ms
Execution time : 177,304.654 ms