explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YWsp

Settings
# exclusive inclusive rows x rows loops node
1. 0.750 15,568.628 ↓ 57.0 57 1

Nested Loop Anti Join (cost=2,275,145.87..2,275,145.93 rows=1 width=6,381) (actual time=14,625.378..15,568.628 rows=57 loops=1)

  • Join Filter: ((pf.id)::text = (f.id)::text)
  • Rows Removed by Join Filter: 6,270
2.          

CTE primary_fields

3. 3.337 540.204 ↓ 110.0 110 1

Hash Join (cost=188.54..185,926.12 rows=1 width=1,157) (actual time=16.173..540.204 rows=110 loops=1)

  • Hash Cond: (w.pdf_page_id = pp.id)
4. 15.766 536.811 ↓ 1.0 24,528 1

Nested Loop (cost=2.12..185,648.82 rows=24,231 width=1,173) (actual time=0.098..536.811 rows=24,528 loops=1)

5. 13.013 324.821 ↓ 1.0 24,528 1

Nested Loop (cost=1.54..127,114.71 rows=24,231 width=1,173) (actual time=0.085..324.821 rows=24,528 loops=1)

6. 3.789 164.167 ↑ 1.2 11,357 1

Nested Loop (cost=0.98..111,304.12 rows=13,842 width=1,194) (actual time=0.061..164.167 rows=11,357 loops=1)

7. 24.094 24.094 ↑ 1.5 11,357 1

Index Scan using fields_trial_run_id_index on fields f_1 (cost=0.42..38,248.47 rows=16,796 width=1,157) (actual time=0.014..24.094 rows=11,357 loops=1)

  • Index Cond: (trial_run_id = '37cf9614-c2a9-443b-bba9-802c08aef136'::uuid)
8. 136.284 136.284 ↑ 1.0 1 11,357

Index Only Scan using word_groups_pkey on word_groups wg (cost=0.56..4.34 rows=1 width=37) (actual time=0.012..0.012 rows=1 loops=11,357)

  • Index Cond: (id = (f_1.word_group_id)::text)
  • Heap Fetches: 2
9. 147.641 147.641 ↑ 6.0 2 11,357

Index Scan using visit_item_field_words_visit_item_field_id_index on word_group_words wgw (cost=0.56..1.02 rows=12 width=53) (actual time=0.013..0.013 rows=2 loops=11,357)

  • Index Cond: ((word_group_id)::text = (wg.id)::text)
10. 196.224 196.224 ↑ 1.0 1 24,528

Index Scan using words_pkey on words w (cost=0.57..2.41 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=24,528)

  • Index Cond: (id = wgw.word_id)
11. 0.008 0.056 ↑ 1.8 25 1

Hash (cost=185.85..185.85 rows=46 width=16) (actual time=0.056..0.056 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
12. 0.020 0.048 ↑ 1.8 25 1

Bitmap Heap Scan on pdf_pages pp (cost=4.78..185.85 rows=46 width=16) (actual time=0.039..0.048 rows=25 loops=1)

  • Recheck Cond: ((pdf_file_id)::text = '97c5a087-0545-4a7d-afea-cef71ac2b2f7'::text)
  • Heap Blocks: exact=3
13. 0.028 0.028 ↑ 1.8 25 1

Bitmap Index Scan on pdf_pages_pdf_id_index (cost=0.00..4.77 rows=46 width=0) (actual time=0.028..0.028 rows=25 loops=1)

  • Index Cond: ((pdf_file_id)::text = '97c5a087-0545-4a7d-afea-cef71ac2b2f7'::text)
14.          

CTE predicted_fields

15. 16.644 15,026.765 ↓ 57.0 57 1

Nested Loop (cost=2.12..2,089,219.75 rows=1 width=1,140) (actual time=14,084.926..15,026.765 rows=57 loops=1)

16. 21.005 14,925.173 ↓ 329.3 28,316 1

Nested Loop (cost=1.70..2,089,180.31 rows=86 width=1,156) (actual time=14,020.808..14,925.173 rows=28,316 loops=1)

17. 4.920 14,677.624 ↓ 329.3 28,318 1

Nested Loop (cost=1.12..2,088,972.56 rows=86 width=1,156) (actual time=14,020.787..14,677.624 rows=28,318 loops=1)

18. 14.447 14,474.772 ↓ 288.5 14,138 1

Nested Loop (cost=0.56..2,088,916.59 rows=49 width=1,177) (actual time=14,020.759..14,474.772 rows=14,138 loops=1)

19. 2.191 14,290.669 ↓ 257.1 14,138 1

Append (cost=0.00..2,088,660.28 rows=55 width=1,139) (actual time=14,020.696..14,290.669 rows=14,138 loops=1)

20. 14,274.522 14,274.522 ↓ 261.8 14,138 1

Seq Scan on field_revisions f_2 (cost=0.00..2,050,327.83 rows=54 width=1,139) (actual time=14,020.695..14,274.522 rows=14,138 loops=1)

  • Filter: ((trial_run_id = '37cf9614-c2a9-443b-bba9-802c08aef136'::uuid) AND ((bot_name)::text = 'ms_subtype_ml_v0.2'::text) AND (revision_id = 0))
  • Rows Removed by Filter: 27,752,711
21. 13.956 13.956 ↓ 0.0 0 1

Index Scan using fields_trial_run_id_index on fields f_3 (cost=0.42..38,332.45 rows=1 width=1,157) (actual time=13.956..13.956 rows=0 loops=1)

  • Index Cond: (trial_run_id = '37cf9614-c2a9-443b-bba9-802c08aef136'::uuid)
  • Filter: (((bot_name)::text = 'ms_subtype_ml_v0.2'::text) AND (revision_id = 0))
  • Rows Removed by Filter: 11,357
22. 169.656 169.656 ↑ 1.0 1 14,138

Index Only Scan using word_groups_pkey on word_groups wg_1 (cost=0.56..4.65 rows=1 width=37) (actual time=0.012..0.012 rows=1 loops=14,138)

  • Index Cond: (id = (f_2.word_group_id)::text)
  • Heap Fetches: 1
23. 197.932 197.932 ↑ 6.0 2 14,138

Index Scan using visit_item_field_words_visit_item_field_id_index on word_group_words wgw_1 (cost=0.56..1.02 rows=12 width=53) (actual time=0.013..0.014 rows=2 loops=14,138)

  • Index Cond: ((word_group_id)::text = (wg_1.id)::text)
24. 226.544 226.544 ↑ 1.0 1 28,318

Index Scan using words_pkey on words w_1 (cost=0.57..2.41 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=28,318)

  • Index Cond: (id = wgw_1.word_id)
25. 84.948 84.948 ↓ 0.0 0 28,316

Index Scan using pdf_pages_pkey on pdf_pages pp_1 (cost=0.43..0.45 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=28,316)

  • Index Cond: (id = w_1.pdf_page_id)
  • Filter: ((pdf_file_id)::text = 'bd50e919-4f93-42c9-849c-b8749fd0a32b'::text)
  • Rows Removed by Filter: 1
26. 15,026.834 15,026.834 ↓ 57.0 57 1

CTE Scan on predicted_fields pf (cost=0.00..0.02 rows=1 width=6,381) (actual time=14,084.933..15,026.834 rows=57 loops=1)

27. 541.044 541.044 ↓ 110.0 110 57

CTE Scan on primary_fields f (cost=0.00..0.02 rows=1 width=516) (actual time=0.284..9.492 rows=110 loops=57)

Planning time : 4.439 ms
Execution time : 15,568.976 ms