explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gmBV

Settings
# exclusive inclusive rows x rows loops node
1. 71.653 3,298.573 ↑ 5.9 113,451 1

Hash Left Join (cost=93,459.74..293,965.52 rows=667,558 width=235) (actual time=2,491.177..3,298.573 rows=113,451 loops=1)

  • Hash Cond: ("*SELECT* 1".name = d.name)
2. 92.268 3,001.359 ↑ 5.9 113,451 1

Hash Anti Join (cost=67,189.90..217,713.76 rows=667,558 width=205) (actual time=2,263.047..3,001.359 rows=113,451 loops=1)

  • Hash Cond: ("*SELECT* 1".name = "*SELECT* 1_1".name)
  • Join Filter: "*SELECT* 1".active
3. 2.626 649.214 ↑ 7.5 118,970 1

Append (cost=26.39..82,910.05 rows=890,077 width=207) (actual time=1.468..649.214 rows=118,970 loops=1)

4. 10.561 638.572 ↑ 6.2 118,646 1

Subquery Scan on *SELECT* 1 (cost=26.39..75,880.26 rows=731,560 width=200) (actual time=1.468..638.572 rows=118,646 loops=1)

5. 113.625 628.011 ↑ 6.2 118,646 1

Hash Join (cost=26.39..68,564.66 rows=731,560 width=248) (actual time=1.466..628.011 rows=118,646 loops=1)

  • Hash Cond: (r.restriction_id = det.id)
6. 512.955 512.955 ↑ 1.0 1,452,796 1

Seq Scan on restricted_domain r (cost=0.00..55,772.31 rows=1,453,431 width=36) (actual time=0.023..512.955 rows=1,452,796 loops=1)

7. 0.073 1.431 ↑ 1.0 151 1

Hash (cost=24.50..24.50 rows=151 width=172) (actual time=1.431..1.431 rows=151 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
8. 1.358 1.358 ↑ 1.0 151 1

Seq Scan on restriction_detail det (cost=0.00..24.50 rows=151 width=172) (actual time=0.031..1.358 rows=151 loops=1)

  • Filter: (active AND (name !~ '^spec'::text) AND (name !~ '^rfc'::text) AND (name !~ '^iana'::text) AND (name !~* '^icann'::text) AND (name !~ '^MIIT'::text) AND (name !~ '^peventive_block'::text))
  • Rows Removed by Filter: 167
9. 4.008 4.008 ↑ 489.2 324 1

Subquery Scan on *SELECT* 2 (cost=30.35..7,029.79 rows=158,517 width=238) (actual time=1.591..4.008 rows=324 loops=1)

10. 0.034 4.008 ↑ 489.2 324 1

Subquery Scan on *SELECT* 2 (cost=30.35..7,029.79 rows=158,517 width=238) (actual time=1.591..4.008 rows=324 loops=1)

11. 0.230 3.974 ↑ 489.2 324 1

Nested Loop (cost=30.35..5,444.62 rows=158,517 width=286) (actual time=1.590..3.974 rows=324 loops=1)

12. 0.552 3.708 ↑ 489.2 12 1

Merge Join (cost=30.35..290.48 rows=5,871 width=194) (actual time=1.566..3.708 rows=12 loops=1)

  • Merge Cond: (w.restriction_id = det_1.id)
13. 2.468 2.468 ↑ 1.0 11,665 1

Index Scan using restricted_word_restriction_id_idx on restricted_word w (cost=0.29..517.00 rows=11,665 width=30) (actual time=0.017..2.468 rows=11,665 loops=1)

14. 0.084 0.688 ↑ 1.0 149 1

Sort (cost=29.96..30.34 rows=151 width=172) (actual time=0.676..0.688 rows=149 loops=1)

  • Sort Key: det_1.id
  • Sort Method: quicksort Memory: 45kB
15. 0.604 0.604 ↑ 1.0 151 1

Seq Scan on restriction_detail det_1 (cost=0.00..24.50 rows=151 width=172) (actual time=0.054..0.604 rows=151 loops=1)

  • Filter: (active AND (name !~ '^spec'::text) AND (name !~ '^rfc'::text) AND (name !~ '^iana'::text) AND (name !~* '^icann'::text) AND (name !~ '^MIIT'::text) AND (name !~ '^peventive_block'::text))
  • Rows Removed by Filter: 167
16. 0.026 0.036 ↑ 1.0 27 12

Materialize (cost=0.00..2.41 rows=27 width=6) (actual time=0.001..0.003 rows=27 loops=12)

17. 0.010 0.010 ↑ 1.0 27 1

Seq Scan on tld t (cost=0.00..2.27 rows=27 width=6) (actual time=0.005..0.010 rows=27 loops=1)

18. 93.194 2,259.877 ↑ 1.4 394,967 1

Hash (cost=57,634.06..57,634.06 rows=548,196 width=16) (actual time=2,259.877..2,259.877 rows=394,967 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,480kB
19. 24.103 2,166.683 ↑ 1.4 394,967 1

Append (cost=26.09..57,634.06 rows=548,196 width=16) (actual time=1.825..2,166.683 rows=394,967 loops=1)

20. 12.347 432.234 ↑ 4.0 113,924 1

Subquery Scan on *SELECT* 1_1 (cost=26.09..53,716.20 rows=450,564 width=13) (actual time=1.825..432.234 rows=113,924 loops=1)

21. 117.747 419.887 ↑ 4.0 113,924 1

Hash Join (cost=26.09..49,210.56 rows=450,564 width=190) (actual time=1.825..419.887 rows=113,924 loops=1)

  • Hash Cond: (r_1.restriction_id = det_2.id)
22. 300.353 300.353 ↑ 1.0 1,452,796 1

Index Only Scan using restricted_domain_pkey on restricted_domain r_1 (cost=0.43..39,228.89 rows=1,453,431 width=17) (actual time=0.029..300.353 rows=1,452,796 loops=1)

  • Heap Fetches: 9,272
23. 0.035 1.787 ↓ 1.1 103 1

Hash (cost=24.50..24.50 rows=93 width=4) (actual time=1.787..1.787 rows=103 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
24. 1.752 1.752 ↓ 1.1 103 1

Seq Scan on restriction_detail det_2 (cost=0.00..24.50 rows=93 width=4) (actual time=0.012..1.752 rows=103 loops=1)

  • Filter: ((name ~ '^spec'::text) OR (name ~ '^rfc'::text) OR (name ~ '^iana'::text) OR (name ~* '^icann'::text) OR (name ~ '^MIIT'::text) OR (name ~ '^peventive_block'::text))
  • Rows Removed by Filter: 215
25. 26.402 1,710.346 ↓ 2.9 281,043 1

Subquery Scan on *SELECT* 2_1 (cost=27.83..3,917.86 rows=97,632 width=32) (actual time=0.873..1,710.346 rows=281,043 loops=1)

26. 1,663.377 1,683.944 ↓ 2.9 281,043 1

Nested Loop (cost=27.83..2,941.54 rows=97,632 width=209) (actual time=0.872..1,683.944 rows=281,043 loops=1)

27. 2.790 10.158 ↓ 2.9 10,409 1

Merge Join (cost=27.83..254.32 rows=3,616 width=26) (actual time=0.838..10.158 rows=10,409 loops=1)

  • Merge Cond: (w_1.restriction_id = det_3.id)
28. 5.110 5.110 ↑ 1.0 11,665 1

Index Scan using restricted_word_restriction_id_idx on restricted_word w_1 (cost=0.29..517.00 rows=11,665 width=30) (actual time=0.013..5.110 rows=11,665 loops=1)

29. 1.465 2.258 ↓ 110.8 10,308 1

Sort (cost=27.54..27.77 rows=93 width=4) (actual time=0.821..2.258 rows=10,308 loops=1)

  • Sort Key: det_3.id
  • Sort Method: quicksort Memory: 29kB
30. 0.793 0.793 ↓ 1.1 103 1

Seq Scan on restriction_detail det_3 (cost=0.00..24.50 rows=93 width=4) (actual time=0.021..0.793 rows=103 loops=1)

  • Filter: ((name ~ '^spec'::text) OR (name ~ '^rfc'::text) OR (name ~ '^iana'::text) OR (name ~* '^icann'::text) OR (name ~ '^MIIT'::text) OR (name ~ '^peventive_block'::text))
  • Rows Removed by Filter: 215
31. 10.386 10.409 ↑ 1.0 27 10,409

Materialize (cost=0.00..2.41 rows=27 width=6) (actual time=0.000..0.001 rows=27 loops=10,409)

32. 0.023 0.023 ↑ 1.0 27 1

Seq Scan on tld t_1 (cost=0.00..2.27 rows=27 width=6) (actual time=0.020..0.023 rows=27 loops=1)

33. 72.199 225.561 ↑ 1.3 264,049 1

Hash (cost=19,025.04..19,025.04 rows=340,304 width=43) (actual time=225.561..225.561 rows=264,049 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,997kB
34. 153.362 153.362 ↑ 1.3 264,049 1

Seq Scan on domain d (cost=0.00..19,025.04 rows=340,304 width=43) (actual time=0.020..153.362 rows=264,049 loops=1)