explain.depesz.com

PostgreSQL's explain analyze made readable

Result: roFW : Optimization for: plan #t6Pz

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.060 9,904.528 ↑ 1.0 10 1

Limit (cost=10,000,270,396.56..10,000,270,396.59 rows=10 width=196) (actual time=9,904.463..9,904.528 rows=10 loops=1)

2. 0.426 9,904.468 ↑ 18.2 10 1

Sort (cost=10,000,270,396.56..10,000,270,397.02 rows=182 width=196) (actual time=9,904.459..9,904.468 rows=10 loops=1)

  • Sort Key: ((h.hit_score * COALESCE(w.weight, '1'::real))) DESC, s_name.string_value
  • Sort Method: top-N heapsort Memory: 27kB
3. 0.319 9,904.042 ↑ 3.0 60 1

Hash Left Join (cost=10,000,216,364.70..10,000,270,392.63 rows=182 width=196) (actual time=8,589.524..9,904.042 rows=60 loops=1)

  • Hash Cond: (s_label.string_value = w.label)
4. 70.121 9,903.680 ↑ 3.0 60 1

Merge Join (cost=10,000,216,363.59..10,000,270,390.37 rows=182 width=196) (actual time=8,589.416..9,903.680 rows=60 loops=1)

  • Merge Cond: (s_abspk.string_id = h.abspk_sid)
5. 481.317 9,833.376 ↑ 1.0 114,320 1

Merge Join (cost=10,000,216,363.44..10,000,270,023.02 rows=119,038 width=176) (actual time=8,588.705..9,833.376 rows=114,320 loops=1)

  • Merge Cond: (s_abspk.string_id = n.abspk_sid)
6. 449.769 449.769 ↑ 1.6 459,937 1

Index Scan using idx_u_sid on interned_string s_abspk (cost=0.42..49,944.01 rows=738,268 width=32) (actual time=0.024..449.769 rows=459,937 loops=1)

7. 136.102 8,902.290 ↑ 1.0 114,320 1

Materialize (cost=10,000,216,186.51..10,000,216,781.70 rows=119,038 width=144) (actual time=8,588.570..8,902.290 rows=114,320 loops=1)

8. 463.568 8,766.188 ↑ 1.0 114,320 1

Sort (cost=10,000,216,186.51..10,000,216,484.11 rows=119,038 width=144) (actual time=8,588.560..8,766.188 rows=114,320 loops=1)

  • Sort Key: n.abspk_sid
  • Sort Method: external merge Disk: 16440kB
9. 588.524 8,302.620 ↑ 1.0 118,612 1

Hash Join (cost=10,000,145,685.41..10,000,197,603.98 rows=119,038 width=144) (actual time=6,055.511..8,302.620 rows=118,612 loops=1)

  • Hash Cond: (n.desc_sid = s_desc.string_id)
10. 552.633 6,262.705 ↑ 1.0 118,612 1

Hash Join (cost=10,000,116,125.38..10,000,156,942.18 rows=119,038 width=128) (actual time=4,596.984..6,262.705 rows=118,612 loops=1)

  • Hash Cond: (n.name_sid = s_name.string_id)
11. 264.432 4,322.697 ↑ 1.0 118,612 1

Hash Join (cost=10,000,086,565.35..10,000,116,744.37 rows=119,038 width=112) (actual time=3,207.850..4,322.697 rows=118,612 loops=1)

  • Hash Cond: (n.label_sid = s_label.string_id)
12. 267.553 2,751.046 ↑ 1.0 118,612 1

Hash Join (cost=10,000,057,005.32..10,000,077,012.57 rows=119,038 width=96) (actual time=1,899.975..2,751.046 rows=118,612 loops=1)

  • Hash Cond: (n.source_sid = s_source.string_id)
13. 141.267 1,180.967 ↑ 1.0 118,612 1

Nested Loop (cost=10,000,027,445.29..10,000,037,744.77 rows=119,038 width=80) (actual time=588.507..1,180.967 rows=118,612 loops=1)

14. 0.067 0.067 ↑ 1.0 1 1

Index Only Scan using idx_u_cover on interned_string s_graph (cost=0.42..8.44 rows=1 width=8) (actual time=0.064..0.067 rows=1 loops=1)

  • Index Cond: (string_id = 69)
  • Heap Fetches: 1
15. 477.329 1,039.633 ↑ 1.0 118,612 1

Merge Join (cost=27,444.87..36,545.95 rows=119,038 width=88) (actual time=588.427..1,039.633 rows=118,612 loops=1)

  • Merge Cond: (n.zone_sid = s_zone.string_id)
16. 112.608 112.608 ↑ 1.0 118,612 1

Index Scan using idx_node_zone on node n (cost=0.42..7,613.52 rows=119,038 width=72) (actual time=0.050..112.608 rows=118,612 loops=1)

  • Index Cond: (graph_sid = 69)
17. 449.696 449.696 ↑ 1.5 502,907 1

Index Scan using idx_u_sid on interned_string s_zone (cost=0.42..49,944.01 rows=738,268 width=32) (actual time=0.021..449.696 rows=502,907 loops=1)

18. 729.861 1,302.526 ↑ 1.0 737,770 1

Hash (cost=15,284.68..15,284.68 rows=738,268 width=32) (actual time=1,302.526..1,302.526 rows=737,770 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3559kB
19. 572.665 572.665 ↑ 1.0 737,770 1

Seq Scan on interned_string s_source (cost=0.00..15,284.68 rows=738,268 width=32) (actual time=0.042..572.665 rows=737,770 loops=1)

20. 731.084 1,307.219 ↑ 1.0 737,770 1

Hash (cost=15,284.68..15,284.68 rows=738,268 width=32) (actual time=1,307.219..1,307.219 rows=737,770 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3559kB
21. 576.135 576.135 ↑ 1.0 737,770 1

Seq Scan on interned_string s_label (cost=0.00..15,284.68 rows=738,268 width=32) (actual time=0.039..576.135 rows=737,770 loops=1)

22. 783.673 1,387.375 ↑ 1.0 737,770 1

Hash (cost=15,284.68..15,284.68 rows=738,268 width=32) (actual time=1,387.375..1,387.375 rows=737,770 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3570kB
23. 603.702 603.702 ↑ 1.0 737,770 1

Seq Scan on interned_string s_name (cost=0.00..15,284.68 rows=738,268 width=32) (actual time=0.034..603.702 rows=737,770 loops=1)

24. 819.868 1,451.391 ↑ 1.0 737,770 1

Hash (cost=15,284.68..15,284.68 rows=738,268 width=32) (actual time=1,451.391..1,451.391 rows=737,770 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3558kB
25. 631.523 631.523 ↑ 1.0 737,770 1

Seq Scan on interned_string s_desc (cost=0.00..15,284.68 rows=738,268 width=32) (actual time=0.032..631.523 rows=737,770 loops=1)

26. 0.183 0.183 ↑ 18.8 60 1

Index Scan using search_hit_pkey on search_hit h (cost=0.15..65.10 rows=1,130 width=44) (actual time=0.024..0.183 rows=60 loops=1)

27. 0.010 0.043 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.042..0.043 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.033 0.033 ↑ 1.0 5 1

Seq Scan on label_weight w (cost=0.00..1.05 rows=5 width=12) (actual time=0.026..0.033 rows=5 loops=1)