explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BFi8

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 9,379.378 ↑ 2.2 5 1

Sort (cost=65,073.72..65,073.75 rows=11 width=89) (actual time=9,379.376..9,379.378 rows=5 loops=1)

  • Sort Key: (((forms.follow_up_email IS NOT NULL) AND ((count(DISTINCT CASE WHEN (answers.follow_up IS TRUE) THEN answers.id ELSE NULL::integer END) > 0) OR (count(DISTINCT CASE WHEN (questions.follow_up IS TRUE) THEN questions.id ELSE NULL::integer END) > 0))))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5331451 dirtied=8, temp read=9619 written=9619
2. 524.493 9,379.356 ↑ 2.2 5 1

GroupAggregate (cost=64,936.05..65,073.53 rows=11 width=89) (actual time=6,602.768..9,379.356 rows=5 loops=1)

  • Group Key: forms.id
  • Buffers: shared hit=5331451 dirtied=8, temp read=9619 written=9619
3. 1,238.449 8,854.863 ↓ 70,769.5 778,464 1

Nested Loop Left Join (cost=64,936.05..65,073.23 rows=11 width=89) (actual time=1,488.317..8,854.863 rows=778,464 loops=1)

  • Buffers: shared hit=5331451 dirtied=8, temp read=9619 written=9619
4. 385.716 2,167.166 ↓ 70,769.5 778,464 1

Merge Left Join (cost=64,926.77..64,926.84 rows=11 width=84) (actual time=1,488.273..2,167.166 rows=778,464 loops=1)

  • Merge Cond: (forms.id = questions.form_id)
  • Buffers: shared hit=185985 dirtied=8, temp read=9619 written=9619
5. 937.444 1,781.357 ↓ 70,769.5 778,464 1

Sort (cost=64,790.35..64,790.38 rows=11 width=79) (actual time=1,488.174..1,781.357 rows=778,464 loops=1)

  • Sort Key: forms.id
  • Sort Method: external sort Disk: 76952kB
  • Buffers: shared hit=185965 dirtied=8, temp read=9619 written=9619
6. 442.310 843.913 ↓ 70,769.5 778,464 1

Hash Right Join (cost=56.68..64,790.16 rows=11 width=79) (actual time=0.080..843.913 rows=778,464 loops=1)

  • Hash Cond: (form_responses.form_id = forms.id)
  • Join Filter: (forms.project_id = 20206)
  • Buffers: shared hit=185965 dirtied=8
7. 401.552 401.552 ↓ 1.1 778,463 1

Index Only Scan using form_responses_form_id_mailing_id_covering_index on form_responses (cost=0.56..61,963.22 rows=734,109 width=16) (actual time=0.017..401.552 rows=778,463 loops=1)

  • Index Cond: (project_id = 20206)
  • Filter: ((deleted IS NOT TRUE) AND (is_unanswered IS NOT TRUE))
  • Rows Removed by Filter: 45736
  • Heap Fetches: 810
  • Buffers: shared hit=185950 dirtied=8
8. 0.003 0.051 ↑ 2.2 5 1

Hash (cost=55.98..55.98 rows=11 width=71) (actual time=0.051..0.051 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=15
9. 0.033 0.048 ↑ 2.2 5 1

Bitmap Heap Scan on forms (cost=4.40..55.98 rows=11 width=71) (actual time=0.026..0.048 rows=5 loops=1)

  • Recheck Cond: (project_id = 20206)
  • Filter: (deleted IS NOT TRUE)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=12
  • Buffers: shared hit=15
10. 0.015 0.015 ↑ 1.2 12 1

Bitmap Index Scan on forms_project_id_index (cost=0.00..4.39 rows=15 width=0) (actual time=0.015..0.015 rows=12 loops=1)

  • Index Cond: (project_id = 20206)
  • Buffers: shared hit=3
11. 0.017 0.093 ↓ 0.0 0 1

Sort (cost=136.42..136.42 rows=1 width=9) (actual time=0.093..0.093 rows=0 loops=1)

  • Sort Key: questions.form_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=20
12. 0.053 0.076 ↓ 0.0 0 1

Bitmap Heap Scan on questions (cost=4.63..136.41 rows=1 width=9) (actual time=0.076..0.076 rows=0 loops=1)

  • Recheck Cond: (project_id = 20206)
  • Filter: ((follow_up IS TRUE) AND (deleted IS NOT TRUE))
  • Rows Removed by Filter: 28
  • Heap Blocks: exact=18
  • Buffers: shared hit=20
13. 0.023 0.023 ↑ 1.6 28 1

Bitmap Index Scan on questions_project_id_index (cost=0.00..4.63 rows=46 width=0) (actual time=0.023..0.023 rows=28 loops=1)

  • Index Cond: (project_id = 20206)
  • Buffers: shared hit=2
14. 778.464 5,449.248 ↓ 0.0 0 778,464

Bitmap Heap Scan on answers (cost=9.28..13.30 rows=1 width=9) (actual time=0.007..0.007 rows=0 loops=778,464)

  • Recheck Cond: ((forms.id = form_id) AND (project_id = 20206))
  • Filter: ((follow_up IS TRUE) AND (deleted IS NOT TRUE))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1253146
  • Buffers: shared hit=5145466
15. 778.464 4,670.784 ↓ 0.0 0 778,464

BitmapAnd (cost=9.28..9.28 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=778,464)

  • Buffers: shared hit=3892320
16. 778.464 778.464 ↑ 2.5 2 778,464

Bitmap Index Scan on answers_form_id_index (cost=0.00..4.32 rows=5 width=0) (actual time=0.001..0.001 rows=2 loops=778,464)

  • Index Cond: (forms.id = form_id)
  • Buffers: shared hit=1556928
17. 3,113.856 3,113.856 ↑ 1.7 33 778,464

Bitmap Index Scan on answers_project_id_index (cost=0.00..4.71 rows=56 width=0) (actual time=0.004..0.004 rows=33 loops=778,464)

  • Index Cond: (project_id = 20206)
  • Buffers: shared hit=2335392