explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DwKA

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 62,466.830 ↓ 0.0 0 1

GroupAggregate (cost=41,705,682.94..41,705,682.99 rows=2 width=40) (actual time=62,466.830..62,466.830 rows=0 loops=1)

  • Group Key: m.contentobjectid
2.          

CTE entries

3. 0.008 0.019 ↑ 100.0 1 1

WindowAgg (cost=0.00..2.75 rows=100 width=48) (actual time=0.018..0.019 rows=1 loops=1)

4. 0.011 0.011 ↑ 100.0 1 1

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

5.          

CTE keywordsearches

6. 0.002 0.026 ↑ 5,000.0 2 1

Nested Loop (cost=0.00..202.00 rows=10,000 width=48) (actual time=0.025..0.026 rows=2 loops=1)

7. 0.001 0.001 ↑ 100.0 1 1

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

8. 0.023 0.023 ↑ 50.0 2 1

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

9.          

CTE matches

10. 0.412 62,465.518 ↑ 5.5 663 1

Recursive Union (cost=654.25..41,705,352.57 rows=3,624 width=77) (actual time=4,022.894..62,465.518 rows=663 loops=1)

11. 16,015.289 31,105.519 ↑ 5.5 663 1

Hash Join (cost=654.25..3,753,612.49 rows=3,614 width=77) (actual time=4,022.892..31,105.519 rows=663 loops=1)

  • Hash Cond: (wp.wordid = w.id)
12. 15,090.177 15,090.177 ↑ 1.0 155,985,207 1

Seq Scan on wordposition wp (cost=0.00..3,167,972.80 rows=155,986,480 width=20) (actual time=0.011..15,090.177 rows=155,985,207 loops=1)

13. 0.002 0.053 ↑ 50.0 1 1

Hash (cost=653.63..653.63 rows=50 width=69) (actual time=0.053..0.053 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.001 0.051 ↑ 50.0 1 1

Nested Loop (cost=0.56..653.63 rows=50 width=69) (actual time=0.049..0.051 rows=1 loops=1)

15. 0.029 0.029 ↑ 50.0 1 1

CTE Scan on keywordsearches kws (cost=0.00..225.00 rows=50 width=48) (actual time=0.027..0.029 rows=1 loops=1)

  • Filter: (index = 0)
  • Rows Removed by Filter: 1
16. 0.021 0.021 ↑ 1.0 1 1

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

  • Index Cond: (wordtext = kws.text)
17. 6.784 31,359.587 ↓ 0.0 0 1

Hash Join (cost=27,786.67..3,795,166.76 rows=1 width=77) (actual time=31,359.587..31,359.587 rows=0 loops=1)

  • Hash Cond: ((kws_1.keywordindex = m_1.keywordindex) AND (wp_1.contentobjectid = m_1.contentobjectid))
  • Join Filter: ((wp_1."position" - m_1."position") = kws_1.index)
  • Rows Removed by Join Filter: 82965
18. 16,227.440 31,352.608 ↑ 363.4 663 1

Hash Join (cost=26,970.81..3,782,302.27 rows=240,936 width=73) (actual time=4,057.296..31,352.608 rows=663 loops=1)

  • Hash Cond: (wp_1.wordid = w_1.id)
19. 15,125.138 15,125.138 ↑ 1.0 155,985,207 1

Seq Scan on wordposition wp_1 (cost=0.00..3,167,972.80 rows=155,986,480 width=20) (actual time=0.006..15,125.138 rows=155,985,207 loops=1)

20. 0.001 0.030 ↑ 3,333.0 1 1

Hash (cost=26,929.14..26,929.14 rows=3,333 width=69) (actual time=0.030..0.030 rows=1 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
21. 0.005 0.029 ↑ 3,333.0 1 1

Nested Loop (cost=0.56..26,929.14 rows=3,333 width=69) (actual time=0.028..0.029 rows=1 loops=1)

22. 0.002 0.002 ↑ 3,333.0 1 1

CTE Scan on keywordsearches kws_1 (cost=0.00..225.00 rows=3,333 width=48) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: (index > 0)
  • Rows Removed by Filter: 1
23. 0.022 0.022 ↑ 1.0 1 1

Index Scan using word_ak on word w_1 (cost=0.56..8.01 rows=1 width=53) (actual time=0.022..0.022 rows=1 loops=1)

  • Index Cond: (wordtext = kws_1.text)
24. 0.084 0.195 ↓ 3.7 663 1

Hash (cost=813.15..813.15 rows=181 width=20) (actual time=0.195..0.195 rows=663 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
25. 0.111 0.111 ↓ 3.7 663 1

WorkTable Scan on matches m_1 (cost=0.00..813.15 rows=181 width=20) (actual time=0.006..0.111 rows=663 loops=1)

  • Filter: (index = 0)
26. 0.004 62,466.828 ↓ 0.0 0 1

Sort (cost=125.61..125.62 rows=2 width=40) (actual time=62,466.828..62,466.828 rows=0 loops=1)

  • Sort Key: m.contentobjectid
  • Sort Method: quicksort Memory: 25kB
27. 0.005 62,466.824 ↓ 0.0 0 1

Hash Join (cost=121.03..125.60 rows=2 width=40) (actual time=62,466.824..62,466.824 rows=0 loops=1)

  • Hash Cond: (m.keywordindex = e.keywordindex)
28. 0.833 62,466.796 ↓ 0.0 0 1

HashAggregate (cost=117.78..122.31 rows=2 width=24) (actual time=62,466.796..62,466.796 rows=0 loops=1)

  • Group Key: m.contentobjectid, m.keywordindex, m.totalitems, m.delta
  • Filter: (count(*) = m.totalitems)
  • Rows Removed by Filter: 663
29. 62,465.963 62,465.963 ↑ 5.5 663 1

CTE Scan on matches m (cost=0.00..72.48 rows=3,624 width=24) (actual time=4,022.896..62,465.963 rows=663 loops=1)

30. 0.002 0.023 ↑ 100.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.021 0.021 ↑ 100.0 1 1

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