explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q8JR

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 107,562.965 ↓ 4.0 4 1

Group (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=107,562.959..107,562.965 rows=4 loops=1)

  • Group Key: books.name, i1.pagenum
2. 0.032 107,562.958 ↓ 4.0 4 1

Sort (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=107,562.956..107,562.958 rows=4 loops=1)

  • Sort Key: books.name, i1.pagenum
  • Sort Method: quicksort Memory: 25kB
3. 0.012 107,562.926 ↓ 4.0 4 1

Nested Loop (cost=3.92..1,809.00 rows=1 width=27) (actual time=11,204.952..107,562.926 rows=4 loops=1)

4. 6.228 6.228 ↑ 1.0 1 1

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

  • Index Cond: (word = 'TEST'::text)
5. 2.630 107,556.686 ↓ 4.0 4 1

Nested Loop (cost=3.50..1,800.56 rows=1 width=35) (actual time=11,198.720..107,556.686 rows=4 loops=1)

6. 31.306 107,322.226 ↓ 1,195.0 1,195 1

Nested Loop (cost=2.94..1,795.84 rows=1 width=71) (actual time=33.069..107,322.226 rows=1,195 loops=1)

7. 61.846 106,935.400 ↓ 17,776.0 17,776 1

Nested Loop (cost=2.52..1,793.40 rows=1 width=79) (actual time=14.639..106,935.400 rows=17,776 loops=1)

  • Join Filter: (books.bookid = i1.bookid)
8. 17.581 87,037.574 ↓ 17,324.0 17,324 1

Nested Loop (cost=1.96..1,788.41 rows=1 width=55) (actual time=13.051..87,037.574 rows=17,324 loops=1)

9. 1.339 1.339 ↑ 1.0 1 1

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

  • Index Cond: (word = 'A'::text)
10. 167.070 87,018.654 ↓ 5,774.7 17,324 1

Nested Loop (cost=1.54..1,779.95 rows=3 width=63) (actual time=11.710..87,018.654 rows=17,324 loops=1)

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

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

12. 0.983 0.983 ↑ 1.0 1 1

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

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

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

14. 2.018 2.018 ↓ 1.0 282 1

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

15. 691.182 691.182 ↓ 91.5 549 282

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

  • Index Cond: ((bookid = books.bookid) AND (words_upper_id = w2.wordid))
  • Heap Fetches: 0
16. 85,901.790 85,901.790 ↓ 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.527..0.555 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. 19,835.980 19,835.980 ↑ 1.0 1 17,324

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

  • Index Cond: ((bookid = i2.bookid) AND (bookposition = (i2.bookposition - 1)))
18. 355.520 355.520 ↓ 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.020..0.020 rows=0 loops=17,776)

  • Index Cond: ((wordid = i1.words_upper_id) AND (word = 'THIS'::text))
  • Heap Fetches: 0
19. 231.830 231.830 ↓ 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.194..0.194 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