explain.depesz.com

PostgreSQL's explain analyze made readable

Result: arRt

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 108,160.507 ↓ 4.0 4 1

Group (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=108,160.501..108,160.507 rows=4 loops=1)

  • Group Key: books.name, i1.pagenum
2. 0.039 108,160.500 ↓ 4.0 4 1

Sort (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=108,160.498..108,160.500 rows=4 loops=1)

  • Sort Key: books.name, i1.pagenum
  • Sort Method: quicksort Memory: 25kB
3. 0.008 108,160.461 ↓ 4.0 4 1

Nested Loop (cost=3.92..1,809.00 rows=1 width=27) (actual time=11,205.443..108,160.461 rows=4 loops=1)

4. 1.744 1.744 ↑ 1.0 1 1

Index Scan using wordsupperlist on words_upper w4 (cost=0.42..8.44 rows=1 width=4) (actual time=1.743..1.744 rows=1 loops=1)

  • Index Cond: (word = 'TEST'::text)
5. 2.114 108,158.709 ↓ 4.0 4 1

Nested Loop (cost=3.50..1,800.56 rows=1 width=35) (actual time=11,203.697..108,158.709 rows=4 loops=1)

6. 43.663 108,002.440 ↓ 1,195.0 1,195 1

Nested Loop (cost=2.94..1,795.84 rows=1 width=71) (actual time=25.680..108,002.440 rows=1,195 loops=1)

7. 74.703 107,674.361 ↓ 17,776.0 17,776 1

Nested Loop (cost=2.52..1,793.40 rows=1 width=79) (actual time=8.467..107,674.361 rows=17,776 loops=1)

  • Join Filter: (books.bookid = i1.bookid)
8. 17.732 84,766.626 ↓ 17,324.0 17,324 1

Nested Loop (cost=1.96..1,788.41 rows=1 width=55) (actual time=7.388..84,766.626 rows=17,324 loops=1)

9. 0.941 0.941 ↑ 1.0 1 1

Index Scan using wordsupperlist on words_upper w3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.940..0.941 rows=1 loops=1)

  • Index Cond: (word = 'A'::text)
10. 133.587 84,747.953 ↓ 5,774.7 17,324 1

Nested Loop (cost=1.54..1,779.95 rows=3 width=63) (actual time=6.444..84,747.953 rows=17,324 loops=1)

  • Join Filter: (books.bookid = i3.bookid)
11. 126.750 879.468 ↓ 1,758.8 154,778 1

Nested Loop (cost=0.98..1,363.05 rows=88 width=43) (actual time=4.819..879.468 rows=154,778 loops=1)

12. 2.976 2.976 ↑ 1.0 1 1

Index Scan using wordsupperlist on words_upper w2 (cost=0.42..8.44 rows=1 width=4) (actual time=2.975..2.976 rows=1 loops=1)

  • Index Cond: (word = 'IS'::text)
13. 128.569 749.742 ↓ 91.8 154,778 1

Nested Loop (cost=0.56..1,337.75 rows=1,686 width=51) (actual time=1.841..749.742 rows=154,778 loops=1)

14. 1.055 1.055 ↓ 1.0 282 1

Seq Scan on books (cost=0.00..5.81 rows=281 width=31) (actual time=0.642..1.055 rows=282 loops=1)

15. 620.118 620.118 ↓ 91.5 549 282

Index Only Scan using instances_bookidwordidposition on instances i2 (cost=0.56..4.68 rows=6 width=20) (actual time=0.917..2.199 rows=549 loops=282)

  • Index Cond: ((bookid = books.bookid) AND (words_upper_id = w2.wordid))
  • Heap Fetches: 0
16. 83,734.898 83,734.898 ↓ 0.0 0 154,778

Index Only Scan using instances_bookidwordidposition on instances i3 (cost=0.56..4.72 rows=1 width=20) (actual time=0.513..0.541 rows=0 loops=154,778)

  • Index Cond: ((bookid = i2.bookid) AND (words_upper_id = w3.wordid))
  • Filter: ((i2.bookposition - 1) = (bookposition - 2))
  • Rows Removed by Filter: 2789
  • Heap Fetches: 0
17. 22,833.032 22,833.032 ↑ 1.0 1 17,324

Index Scan using instancesbooksort on instances i1 (cost=0.56..4.97 rows=1 width=24) (actual time=1.308..1.318 rows=1 loops=17,324)

  • Index Cond: ((bookid = i2.bookid) AND (bookposition = (i2.bookposition - 1)))
18. 284.416 284.416 ↓ 0.0 0 17,776

Index Only Scan using wordsupper on words_upper w1 (cost=0.42..2.43 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=17,776)

  • Index Cond: ((wordid = i1.words_upper_id) AND (word = 'THIS'::text))
  • Heap Fetches: 0
19. 154.155 154.155 ↓ 0.0 0 1,195

Index Only Scan using instances_bookidwordidposition on instances i4 (cost=0.56..4.71 rows=1 width=20) (actual time=0.129..0.129 rows=0 loops=1,195)

  • Index Cond: ((bookid = i1.bookid) AND (words_upper_id = w4.wordid))
  • Filter: (i1.bookposition = (bookposition - 3))
  • Rows Removed by Filter: 106
  • Heap Fetches: 0