explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CGLM

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 40,108.125 ↓ 4.0 4 1

Group (cost=6,895.98..6,895.99 rows=1 width=27) (actual time=40,108.119..40,108.125 rows=4 loops=1)

  • Group Key: books.name, i1.pagenum
2. 0.027 40,108.118 ↓ 4.0 4 1

Sort (cost=6,895.98..6,895.98 rows=1 width=27) (actual time=40,108.116..40,108.118 rows=4 loops=1)

  • Sort Key: books.name, i1.pagenum
  • Sort Method: quicksort Memory: 25kB
3. 0.016 40,108.091 ↓ 4.0 4 1

Nested Loop (cost=36.97..6,895.97 rows=1 width=27) (actual time=5,578.079..40,108.091 rows=4 loops=1)

4. 0.012 40,105.967 ↓ 4.0 4 1

Nested Loop (cost=36.82..6,894.46 rows=1 width=36) (actual time=5,577.343..40,105.967 rows=4 loops=1)

5. 4.272 4.272 ↑ 1.0 1 1

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

  • Index Cond: (word = 'TEST'::text)
6. 2.902 40,101.683 ↓ 4.0 4 1

Nested Loop (cost=36.40..6,886.01 rows=1 width=44) (actual time=5,573.068..40,101.683 rows=4 loops=1)

7. 1.525 39,421.216 ↓ 1,195.0 1,195 1

Nested Loop (cost=35.84..6,859.11 rows=1 width=40) (actual time=156.084..39,421.216 rows=1,195 loops=1)

8. 1.190 1.190 ↑ 1.0 1 1

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

  • Index Cond: (word = 'A'::text)
9. 18.940 39,418.501 ↓ 1,195.0 1,195 1

Nested Loop (cost=35.42..6,850.66 rows=1 width=48) (actual time=154.892..39,418.501 rows=1,195 loops=1)

10. 88.572 21,429.819 ↓ 7,287.0 7,287 1

Nested Loop (cost=34.86..6,823.75 rows=1 width=28) (actual time=59.191..21,429.819 rows=7,287 loops=1)

  • Join Filter: (i1.words_upper_id = w1.wordid)
  • Rows Removed by Join Filter: 149923
11. 2.957 2.957 ↑ 1.0 1 1

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

  • Index Cond: (word = 'THIS'::text)
12. 317.272 21,338.290 ↓ 1,807.0 157,210 1

Nested Loop (cost=34.44..6,814.23 rows=87 width=36) (actual time=53.926..21,338.290 rows=157,210 loops=1)

13. 131.112 1,364.212 ↓ 1,758.8 154,778 1

Nested Loop (cost=33.88..6,375.69 rows=88 width=12) (actual time=52.687..1,364.212 rows=154,778 loops=1)

14. 0.996 0.996 ↑ 1.0 1 1

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

  • Index Cond: (word = 'IS'::text)
15. 1,188.102 1,232.104 ↓ 92.1 154,778 1

Bitmap Heap Scan on instances i2 (cost=33.46..6,350.46 rows=1,680 width=20) (actual time=51.687..1,232.104 rows=154,778 loops=1)

  • Recheck Cond: (words_upper_id = w2.wordid)
  • Rows Removed by Index Recheck: 5566449
  • Heap Blocks: exact=34853 lossy=52916
16. 44.002 44.002 ↓ 92.1 154,778 1

Bitmap Index Scan on instances_wordsupperid (cost=0.00..33.04 rows=1,680 width=0) (actual time=44.002..44.002 rows=154,778 loops=1)

  • Index Cond: (words_upper_id = w2.wordid)
17. 19,656.806 19,656.806 ↑ 1.0 1 154,778

Index Scan using instancesbooksort on instances i1 (cost=0.56..4.97 rows=1 width=24) (actual time=0.125..0.127 rows=1 loops=154,778)

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

Index Scan using instances_bookidwordid on instances i3 (cost=0.56..26.90 rows=1 width=20) (actual time=2.239..2.466 rows=0 loops=7,287)

  • Index Cond: ((bookid = i1.bookid) AND (words_upper_id = w3.wordid))
  • Filter: (i1.bookposition = (bookposition - 2))
  • Rows Removed by Filter: 2812
19. 677.565 677.565 ↓ 0.0 0 1,195

Index Scan using instances_bookidwordid on instances i4 (cost=0.56..26.90 rows=1 width=20) (actual time=0.562..0.567 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
20. 2.108 2.108 ↑ 1.0 1 4

Index Scan using books_pkey on books (cost=0.15..1.50 rows=1 width=31) (actual time=0.526..0.527 rows=1 loops=4)

  • Index Cond: (bookid = i1.bookid)