explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZwBd

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 102,853.125 ↓ 4.0 4 1

Group (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=102,853.117..102,853.125 rows=4 loops=1)

  • Group Key: books.name, i1.pagenum
2. 0.031 102,853.116 ↓ 4.0 4 1

Sort (cost=1,809.01..1,809.02 rows=1 width=27) (actual time=102,853.114..102,853.116 rows=4 loops=1)

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

Nested Loop (cost=3.92..1,809.00 rows=1 width=27) (actual time=10,783.306..102,853.085 rows=4 loops=1)

4. 1.940 1.940 ↑ 1.0 1 1

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

  • Index Cond: (word = 'TEST'::text)
5. 2.911 102,851.136 ↓ 4.0 4 1

Nested Loop (cost=3.50..1,800.56 rows=1 width=35) (actual time=10,781.364..102,851.136 rows=4 loops=1)

6. 32.407 102,722.750 ↓ 1,195.0 1,195 1

Nested Loop (cost=2.94..1,795.84 rows=1 width=71) (actual time=23.396..102,722.750 rows=1,195 loops=1)

7. 70.889 102,405.927 ↓ 17,776.0 17,776 1

Nested Loop (cost=2.52..1,793.40 rows=1 width=79) (actual time=6.039..102,405.927 rows=17,776 loops=1)

  • Join Filter: (books.bookid = i1.bookid)
8. 18.541 83,833.006 ↓ 17,324.0 17,324 1

Nested Loop (cost=1.96..1,788.41 rows=1 width=55) (actual time=4.464..83,833.006 rows=17,324 loops=1)

9. 1.043 1.043 ↑ 1.0 1 1

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

  • Index Cond: (word = 'A'::text)
10. 219.752 83,813.422 ↓ 5,774.7 17,324 1

Nested Loop (cost=1.54..1,779.95 rows=3 width=63) (actual time=3.419..83,813.422 rows=17,324 loops=1)

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

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

12. 1.009 1.009 ↑ 1.0 1 1

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

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

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

14. 2.359 2.359 ↓ 1.0 282 1

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

15. 526.494 526.494 ↓ 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.707..1.867 rows=549 loops=282)

  • Index Cond: ((bookid = books.bookid) AND (words_upper_id = w2.wordid))
  • Heap Fetches: 0
16. 82,806.230 82,806.230 ↓ 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.508..0.535 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,502.032 18,502.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.059..1.068 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. 125.475 125.475 ↓ 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.105..0.105 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