explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t6Pz

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.117 9,705.159 ↑ 1.0 10 1

Limit (cost=345,806.76..345,806.78 rows=10 width=196) (actual time=9,705.048..9,705.159 rows=10 loops=1)

2. 0.524 9,705.042 ↑ 18.2 10 1

Sort (cost=345,806.76..345,807.21 rows=182 width=196) (actual time=9,705.030..9,705.042 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.542 9,704.518 ↑ 3.0 60 1

Nested Loop Left Join (cost=278,889.94..345,802.82 rows=182 width=196) (actual time=7,604.110..9,704.518 rows=60 loops=1)

  • Join Filter: (w.label = s_label.string_value)
  • Rows Removed by Join Filter: 242
4. 82.959 9,703.676 ↑ 3.0 60 1

Hash Join (cost=278,889.81..345,776.50 rows=182 width=196) (actual time=7,604.055..9,703.676 rows=60 loops=1)

  • Hash Cond: (s_abspk.string_id = h.abspk_sid)
5. 1,189.163 9,620.600 ↑ 1.0 118,612 1

Hash Join (cost=278,810.58..345,249.06 rows=119,038 width=176) (actual time=7,597.595..9,620.600 rows=118,612 loops=1)

  • Hash Cond: (s_abspk.string_id = n.abspk_sid)
6. 835.219 835.219 ↑ 1.0 737,770 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.025..835.219 rows=737,770 loops=1)

7. 189.311 7,596.218 ↑ 1.0 118,612 1

Hash (cost=274,880.18..274,880.18 rows=119,038 width=144) (actual time=7,596.217..7,596.218 rows=118,612 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 2650kB
8. 657.193 7,406.907 ↑ 1.0 118,612 1

Merge Join (cost=221,507.39..274,880.18 rows=119,038 width=144) (actual time=5,913.240..7,406.907 rows=118,612 loops=1)

  • Merge Cond: (n.name_sid = s_name.string_id)
9. 444.784 6,048.556 ↑ 1.0 118,612 1

Sort (cost=221,341.34..221,638.93 rows=119,038 width=128) (actual time=5,913.139..6,048.556 rows=118,612 loops=1)

  • Sort Key: n.name_sid
  • Sort Method: external merge Disk: 14208kB
10. 658.526 5,603.772 ↑ 1.0 118,612 1

Merge Join (cost=150,203.26..203,574.30 rows=119,038 width=128) (actual time=4,117.141..5,603.772 rows=118,612 loops=1)

  • Merge Cond: (n.desc_sid = s_desc.string_id)
11. 374.971 4,246.318 ↑ 1.0 118,612 1

Sort (cost=150,035.46..150,333.05 rows=119,038 width=112) (actual time=4,117.029..4,246.318 rows=118,612 loops=1)

  • Sort Key: n.desc_sid
  • Sort Method: external merge Disk: 10744kB
12. 481.003 3,871.347 ↑ 1.0 118,612 1

Merge Join (cost=104,389.56..133,080.42 rows=119,038 width=112) (actual time=2,842.819..3,871.347 rows=118,612 loops=1)

  • Merge Cond: (n.source_sid = s_source.string_id)
13. 291.064 2,957.147 ↑ 1.0 118,612 1

Sort (cost=104,364.04..104,661.63 rows=119,038 width=96) (actual time=2,842.746..2,957.147 rows=118,612 loops=1)

  • Sort Key: n.source_sid
  • Sort Method: external merge Disk: 7984kB
14. 555.198 2,666.083 ↑ 1.0 118,612 1

Merge Join (cost=80,516.75..88,224.50 rows=119,038 width=96) (actual time=2,072.539..2,666.083 rows=118,612 loops=1)

  • Merge Cond: (n.label_sid = s_label.string_id)
15. 300.233 1,560.014 ↑ 1.0 118,612 1

Sort (cost=53,072.30..53,369.90 rows=119,038 width=80) (actual time=1,447.743..1,560.014 rows=118,612 loops=1)

  • Sort Key: n.label_sid
  • Sort Method: external merge Disk: 8112kB
16. 152.252 1,259.781 ↑ 1.0 118,612 1

Nested Loop (cost=27,445.29..37,744.77 rows=119,038 width=80) (actual time=623.718..1,259.781 rows=118,612 loops=1)

17. 0.027 0.027 ↑ 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.022..0.027 rows=1 loops=1)

  • Index Cond: (string_id = 69)
  • Heap Fetches: 1
18. 519.366 1,107.502 ↑ 1.0 118,612 1

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

  • Merge Cond: (n.zone_sid = s_zone.string_id)
19. 115.741 115.741 ↑ 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.024..115.741 rows=118,612 loops=1)

  • Index Cond: (graph_sid = 69)
20. 472.395 472.395 ↑ 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.010..472.395 rows=502,907 loops=1)

21. 550.871 550.871 ↑ 1.3 586,309 1

Index Scan using idx_u_sid on interned_string s_label (cost=0.42..49,944.01 rows=738,268 width=32) (actual time=0.042..550.871 rows=586,309 loops=1)

22. 433.197 433.197 ↑ 1.5 497,735 1

Index Scan using idx_u_sid on interned_string s_source (cost=0.42..49,944.01 rows=738,268 width=32) (actual time=0.047..433.197 rows=497,735 loops=1)

23. 698.928 698.928 ↓ 1.0 746,364 1

Index Scan using idx_u_sid on interned_string s_desc (cost=0.42..49,944.01 rows=738,268 width=32) (actual time=0.040..698.928 rows=746,364 loops=1)

24. 701.158 701.158 ↓ 1.0 747,259 1

Index Scan using idx_u_sid on interned_string s_name (cost=0.42..49,944.01 rows=738,268 width=32) (actual time=0.031..701.158 rows=747,259 loops=1)

25. 0.046 0.117 ↑ 18.8 60 1

Hash (cost=65.10..65.10 rows=1,130 width=44) (actual time=0.116..0.117 rows=60 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 20kB
26. 0.071 0.071 ↑ 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.014..0.071 rows=60 loops=1)

27. 0.280 0.300 ↑ 1.0 5 60

Materialize (cost=0.13..12.23 rows=5 width=12) (actual time=0.001..0.005 rows=5 loops=60)

28. 0.020 0.020 ↑ 1.0 5 1

Index Only Scan using idx_cov_lw on label_weight w (cost=0.13..12.21 rows=5 width=12) (actual time=0.013..0.020 rows=5 loops=1)

  • Heap Fetches: 5