explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dxno

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

Sort (cost=65,050.44..65,050.47 rows=11 width=89) (actual time=9,332.522..9,332.522 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=5331366 dirtied=1, temp read=9619 written=9619
2. 517.747 9,332.500 ↑ 2.2 5 1

GroupAggregate (cost=64,912.77..65,050.25 rows=11 width=89) (actual time=6,572.305..9,332.500 rows=5 loops=1)

  • Group Key: forms.id
  • Buffers: shared hit=5331366 dirtied=1, temp read=9619 written=9619
3. 1,178.828 8,814.753 ↓ 70,768.5 778,454 1

Nested Loop Left Join (cost=64,912.77..65,049.95 rows=11 width=89) (actual time=1,522.670..8,814.753 rows=778,454 loops=1)

  • Buffers: shared hit=5331366 dirtied=1, temp read=9619 written=9619
4. 386.924 2,186.747 ↓ 70,768.5 778,454 1

Merge Left Join (cost=64,903.49..64,903.56 rows=11 width=84) (actual time=1,522.624..2,186.747 rows=778,454 loops=1)

  • Merge Cond: (forms.id = questions.form_id)
  • Buffers: shared hit=185965 dirtied=1, temp read=9619 written=9619
5. 935.792 1,799.720 ↓ 70,768.5 778,454 1

Sort (cost=64,767.07..64,767.10 rows=11 width=79) (actual time=1,522.515..1,799.720 rows=778,454 loops=1)

  • Sort Key: forms.id
  • Sort Method: external sort Disk: 76952kB
  • Buffers: shared hit=185945 dirtied=1, temp read=9619 written=9619
6. 455.532 863.928 ↓ 70,768.5 778,454 1

Hash Right Join (cost=56.68..64,766.88 rows=11 width=79) (actual time=0.087..863.928 rows=778,454 loops=1)

  • Hash Cond: (form_responses.form_id = forms.id)
  • Join Filter: (forms.project_id = 20206)
  • Buffers: shared hit=185945 dirtied=1
7. 408.341 408.341 ↓ 1.1 778,453 1

Index Only Scan using form_responses_form_id_mailing_id_covering_index on form_responses (cost=0.56..61,939.97 rows=734,101 width=16) (actual time=0.017..408.341 rows=778,453 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: 778
  • Buffers: shared hit=185930 dirtied=1
8. 0.006 0.055 ↑ 2.2 5 1

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

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

Bitmap Heap Scan on forms (cost=4.40..55.98 rows=11 width=71) (actual time=0.028..0.049 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.019 0.019 ↑ 1.2 12 1

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

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

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

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

Bitmap Heap Scan on questions (cost=4.63..136.41 rows=1 width=9) (actual time=0.083..0.083 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.028 0.028 ↑ 1.6 28 1

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

  • Index Cond: (project_id = 20206)
  • Buffers: shared hit=2
14. 778.454 5,449.178 ↓ 0.0 0 778,454

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

  • 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=1253131
  • Buffers: shared hit=5145401
15. 778.454 4,670.724 ↓ 0.0 0 778,454

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

  • Buffers: shared hit=3892270
16. 778.454 778.454 ↑ 2.5 2 778,454

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,454)

  • Index Cond: (forms.id = form_id)
  • Buffers: shared hit=1556908
17. 3,113.816 3,113.816 ↑ 1.7 33 778,454

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,454)

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