explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 00l

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 52,443.360 ↓ 4.0 4 1

Group (cost=6,899.60..6,899.61 rows=1 width=27) (actual time=52,443.354..52,443.360 rows=4 loops=1)

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

Sort (cost=6,899.60..6,899.61 rows=1 width=27) (actual time=52,443.351..52,443.353 rows=4 loops=1)

  • Sort Key: books.name, i1.pagenum
  • Sort Method: quicksort Memory: 25kB
3. 0.019 52,443.326 ↓ 4.0 4 1

Nested Loop (cost=36.97..6,899.59 rows=1 width=27) (actual time=7,594.453..52,443.326 rows=4 loops=1)

4. 0.009 52,441.291 ↓ 4.0 4 1

Nested Loop (cost=36.82..6,898.09 rows=1 width=36) (actual time=7,593.829..52,441.291 rows=4 loops=1)

5. 4.954 4.954 ↑ 1.0 1 1

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

  • Index Cond: (word = 'TEST'::text)
6. 2.315 52,436.328 ↓ 4.0 4 1

Nested Loop (cost=36.40..6,889.64 rows=1 width=44) (actual time=7,588.872..52,436.328 rows=4 loops=1)

7. 1.474 51,865.193 ↓ 1,195.0 1,195 1

Nested Loop (cost=35.84..6,860.92 rows=1 width=40) (actual time=226.783..51,865.193 rows=1,195 loops=1)

8. 2.218 2.218 ↑ 1.0 1 1

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

  • Index Cond: (word = 'A'::text)
9. 16.796 51,861.501 ↓ 1,195.0 1,195 1

Nested Loop (cost=35.42..6,852.47 rows=1 width=48) (actual time=224.561..51,861.501 rows=1,195 loops=1)

10. 90.736 22,660.270 ↓ 7,287.0 7,287 1

Nested Loop (cost=34.86..6,823.75 rows=1 width=28) (actual time=75.624..22,660.270 rows=7,287 loops=1)

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

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

  • Index Cond: (word = 'THIS'::text)
12. 411.931 22,567.510 ↓ 1,807.0 157,210 1

Nested Loop (cost=34.44..6,814.23 rows=87 width=36) (actual time=69.607..22,567.510 rows=157,210 loops=1)

13. 133.444 1,415.327 ↓ 1,758.8 154,778 1

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

14. 0.970 0.970 ↑ 1.0 1 1

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

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

Bitmap Heap Scan on instances i2 (cost=33.46..6,350.46 rows=1,680 width=20) (actual time=66.412..1,280.913 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. 57.997 57.997 ↓ 92.1 154,778 1

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

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

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

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

Index Scan using instances_wordsupperidbookpage on instances i3 (cost=0.56..28.71 rows=1 width=20) (actual time=3.649..4.005 rows=0 loops=7,287)

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

Index Scan using instances_wordsupperidbookpage on instances i4 (cost=0.56..28.71 rows=1 width=20) (actual time=0.470..0.476 rows=0 loops=1,195)

  • Index Cond: ((words_upper_id = w4.wordid) AND (bookid = i1.bookid))
  • Filter: (i1.bookposition = (bookposition - 3))
  • Rows Removed by Filter: 106
20. 2.016 2.016 ↑ 1.0 1 4

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

  • Index Cond: (bookid = i1.bookid)