explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JQYU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=469,855,506.73..469,855,518.31 rows=200 width=40) (actual rows= loops=)

  • Group Key: m.contentobjectid
2.          

CTE entries

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.00..2.75 rows=100 width=48) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Function Scan on json_array_elements (cost=0.00..1.00 rows=100 width=36) (actual rows= loops=)

5.          

CTE keywordsearches

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..202.00 rows=10,000 width=48) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

CTE Scan on entries (cost=0.00..2.00 rows=100 width=44) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Function Scan on json_to_recordset t (cost=0.00..1.00 rows=100 width=36) (actual rows= loops=)

9.          

CTE word_matches

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..23,504.00 rows=10,000 width=69) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

CTE Scan on keywordsearches kws (cost=0.00..200.00 rows=10,000 width=48) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using word_ak on word w (cost=0.56..2.33 rows=1 width=53) (actual rows= loops=)

  • Index Cond: (wordtext = kws.text)
13.          

CTE matches

14. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.57..469,686,081.72 rows=4,314,615 width=64) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..2,151,441.35 rows=2,219,055 width=64) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

CTE Scan on word_matches wm (cost=0.00..225.00 rows=50 width=56) (actual rows= loops=)

  • Filter: (index = 0)
17. 0.000 0.000 ↓ 0.0

Index Scan using wordposition_wordid_idx on wordposition wp (cost=0.57..42,580.52 rows=44,381 width=20) (actual rows= loops=)

  • Index Cond: (wordid = wm.id)
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=509,005.61..46,744,834.81 rows=209,556 width=64) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Merge Join (cost=509,005.03..536,757.18 rows=1,849,032 width=68) (actual rows= loops=)

  • Merge Cond: (wm_1.keywordindex = m_1.keywordindex)
20. 0.000 0.000 ↓ 0.0

Sort (cost=420.02..428.36 rows=3,333 width=56) (actual rows= loops=)

  • Sort Key: wm_1.keywordindex
21. 0.000 0.000 ↓ 0.0

CTE Scan on word_matches wm_1 (cost=0.00..225.00 rows=3,333 width=56) (actual rows= loops=)

  • Filter: (index > 0)
22. 0.000 0.000 ↓ 0.0

Sort (cost=508,585.01..508,862.39 rows=110,953 width=20) (actual rows= loops=)

  • Sort Key: m_1.keywordindex
23. 0.000 0.000 ↓ 0.0

WorkTable Scan on matches m_1 (cost=0.00..499,287.38 rows=110,953 width=20) (actual rows= loops=)

  • Filter: (index = 0)
24. 0.000 0.000 ↓ 0.0

Index Scan using wordposition_wordid_contentobjectid_idx on wordposition wp_1 (cost=0.57..24.98 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((wordid = wm_1.id) AND (contentobjectid = m_1.contentobjectid))
  • Filter: (("position" - m_1."position") = wm_1.index)
25. 0.000 0.000 ↓ 0.0

Sort (cost=145,716.25..145,718.95 rows=1,078 width=40) (actual rows= loops=)

  • Sort Key: m.contentobjectid
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=140,228.24..145,661.95 rows=1,078 width=40) (actual rows= loops=)

  • Hash Cond: (m.keywordindex = e.keywordindex)
27. 0.000 0.000 ↓ 0.0

HashAggregate (cost=140,224.99..145,618.26 rows=2,157 width=24) (actual rows= loops=)

  • Group Key: m.contentobjectid, m.keywordindex, m.totalitems, m.delta
  • Filter: (count(*) = m.totalitems)
28. 0.000 0.000 ↓ 0.0

CTE Scan on matches m (cost=0.00..86,292.30 rows=4,314,615 width=24) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=2.00..2.00 rows=100 width=40) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

CTE Scan on entries e (cost=0.00..2.00 rows=100 width=40) (actual rows= loops=)