explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WuPJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 24,539.187 ↑ 2.2 5 1

Sort (cost=65,098.03..65,098.06 rows=11 width=89) (actual time=24,539.186..24,539.187 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=15622468 dirtied=3, temp read=9619 written=9619
2. 1,502.703 24,539.167 ↑ 2.2 5 1

GroupAggregate (cost=64,960.19..65,097.84 rows=11 width=89) (actual time=16,272.781..24,539.167 rows=5 loops=1)

  • Group Key: forms.id
  • Buffers: shared hit=15622468 dirtied=3, temp read=9619 written=9619
3. 2,975.805 23,036.464 ↓ 212,309.5 2,335,404 1

Nested Loop Left Join (cost=64,960.19..65,097.54 rows=11 width=89) (actual time=1,510.365..23,036.464 rows=2,335,404 loops=1)

  • Buffers: shared hit=15622468 dirtied=3, temp read=9619 written=9619
4. 1,390.424 3,712.831 ↓ 212,309.5 2,335,404 1

Merge Left Join (cost=64,950.91..64,951.15 rows=11 width=84) (actual time=1,510.318..3,712.831 rows=2,335,404 loops=1)

  • Merge Cond: (forms.id = questions.form_id)
  • Buffers: shared hit=185992 dirtied=3, temp read=9619 written=9619
5. 944.410 1,798.803 ↓ 70,769.8 778,468 1

Sort (cost=64,813.59..64,813.62 rows=11 width=79) (actual time=1,510.193..1,798.803 rows=778,468 loops=1)

  • Sort Key: forms.id
  • Sort Method: external sort Disk: 76952kB
  • Buffers: shared hit=185972 dirtied=3, temp read=9619 written=9619
6. 453.194 854.393 ↓ 70,769.8 778,468 1

Hash Right Join (cost=56.68..64,813.40 rows=11 width=79) (actual time=0.072..854.393 rows=778,468 loops=1)

  • Hash Cond: (form_responses.form_id = forms.id)
  • Join Filter: (forms.project_id = 20206)
  • Buffers: shared hit=185972 dirtied=3
7. 401.151 401.151 ↓ 1.1 778,467 1

Index Only Scan using form_responses_form_id_mailing_id_covering_index on form_responses (cost=0.56..61,986.43 rows=734,118 width=16) (actual time=0.017..401.151 rows=778,467 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: 808
  • Buffers: shared hit=185957 dirtied=3
8. 0.003 0.048 ↑ 2.2 5 1

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

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

Bitmap Heap Scan on forms (cost=4.40..55.98 rows=11 width=71) (actual time=0.024..0.045 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.014 0.014 ↑ 1.2 12 1

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

  • Index Cond: (project_id = 20206)
  • Buffers: shared hit=3
11. 523.516 523.604 ↓ 66,724.3 2,335,350 1

Sort (cost=137.31..137.40 rows=35 width=9) (actual time=0.118..523.604 rows=2,335,350 loops=1)

  • Sort Key: questions.form_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=20
12. 0.061 0.088 ↑ 2.3 15 1

Bitmap Heap Scan on questions (cost=4.64..136.42 rows=35 width=9) (actual time=0.049..0.088 rows=15 loops=1)

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

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

  • Index Cond: (project_id = 20206)
  • Buffers: shared hit=2
14. 2,335.404 16,347.828 ↓ 0.0 0 2,335,404

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

  • 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=3759456
  • Buffers: shared hit=15436476
15. 2,335.404 14,012.424 ↓ 0.0 0 2,335,404

BitmapAnd (cost=9.28..9.28 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=2,335,404)

  • Buffers: shared hit=11677020
16. 2,335.404 2,335.404 ↑ 2.5 2 2,335,404

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=2,335,404)

  • Index Cond: (forms.id = form_id)
  • Buffers: shared hit=4670808
17. 9,341.616 9,341.616 ↑ 1.7 33 2,335,404

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=2,335,404)

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