explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iYcL

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 105,737.065 ↓ 4.0 4 1

Group (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=105,737.059..105,737.065 rows=4 loops=1)

  • Group Key: books.name, i1.pagenum
2. 0.045 105,737.059 ↓ 4.0 4 1

Sort (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=105,737.057..105,737.059 rows=4 loops=1)

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

Nested Loop (cost=3.92..1,809.00 rows=1 width=27) (actual time=11,252.864..105,737.014 rows=4 loops=1)

4. 1.986 1.986 ↑ 1.0 1 1

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

  • Index Cond: (word = 'TEST'::text)
5. 2.903 105,735.020 ↓ 4.0 4 1

Nested Loop (cost=3.50..1,800.56 rows=1 width=35) (actual time=11,250.875..105,735.020 rows=4 loops=1)

6. 30.696 105,479.972 ↓ 1,195.0 1,195 1

Nested Loop (cost=2.94..1,795.84 rows=1 width=71) (actual time=38.391..105,479.972 rows=1,195 loops=1)

7. 74.303 104,880.444 ↓ 17,776.0 17,776 1

Nested Loop (cost=2.52..1,793.40 rows=1 width=79) (actual time=10.051..104,880.444 rows=17,776 loops=1)

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

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

9. 1.417 1.417 ↑ 1.0 1 1

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

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

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

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

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

12. 1.423 1.423 ↑ 1.0 1 1

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

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

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

14. 1.907 1.907 ↓ 1.0 282 1

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

15. 750.120 750.120 ↓ 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.203..2.660 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.514..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. 19,887.952 19,887.952 ↑ 1.0 1 17,324

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

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

  • Index Cond: ((wordid = i1.words_upper_id) AND (word = 'THIS'::text))
  • Heap Fetches: 0
19. 252.145 252.145 ↓ 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.211..0.211 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
Planning time : 61.605 ms
Execution time : 105,737.253 ms