explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E9ur

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

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

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

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

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

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

4. 4.136 4.136 ↑ 1.0 1 1

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

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

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

6. 38.319 104,994.989 ↓ 1,195.0 1,195 1

Nested Loop (cost=2.94..1,795.84 rows=1 width=71) (actual time=35.518..104,994.989 rows=1,195 loops=1)

7. 66.738 104,458.942 ↓ 17,776.0 17,776 1

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

  • Join Filter: (books.bookid = i1.bookid)
8. 17.487 85,405.100 ↓ 17,324.0 17,324 1

Nested Loop (cost=1.96..1,788.41 rows=1 width=55) (actual time=3.974..85,405.100 rows=17,324 loops=1)

9. 0.981 0.981 ↑ 1.0 1 1

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

  • Index Cond: (word = 'A'::text)
10. 271.895 85,386.632 ↓ 5,774.7 17,324 1

Nested Loop (cost=1.54..1,779.95 rows=3 width=63) (actual time=2.992..85,386.632 rows=17,324 loops=1)

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

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

12. 0.973 0.973 ↑ 1.0 1 1

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

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

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

14. 1.488 1.488 ↓ 1.0 282 1

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

15. 657.906 657.906 ↓ 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.091..2.333 rows=549 loops=282)

  • Index Cond: ((bookid = books.bookid) AND (words_upper_id = w2.wordid))
  • Heap Fetches: 0
16. 84,199.232 84,199.232 ↓ 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.517..0.544 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. 18,987.104 18,987.104 ↑ 1.0 1 17,324

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

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

  • Index Cond: ((wordid = i1.words_upper_id) AND (word = 'THIS'::text))
  • Heap Fetches: 0
19. 223.465 223.465 ↓ 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.187..0.187 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 : 48.043 ms
Execution time : 105,225.194 ms