explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wRoY

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 7,051.120 ↑ 1.0 1 1

Limit (cost=308,153.00..308,153.00 rows=1 width=192) (actual time=7,051.119..7,051.120 rows=1 loops=1)

  • Output: mismatches.id, mismatches.mismatchable_type, mismatches.mismatchable_id, mismatches.amount, mismatches.status, mismatches.created_at, mismatches.updated_at, mismatches.mws_credential_id, mismatches.reimbursement_status, mismatches.special_alert, mismatches.payment_status, mismatches.viewed_at, mismatches.viewer_id, mismatches.response_labels, (COALESCE((SubPlan 2), (SubPlan 3)))
  • Buffers: shared hit=62190 read=1951767
2. 0.940 7,051.116 ↑ 31.0 1 1

Sort (cost=308,153.00..308,153.08 rows=31 width=192) (actual time=7,051.116..7,051.116 rows=1 loops=1)

  • Output: mismatches.id, mismatches.mismatchable_type, mismatches.mismatchable_id, mismatches.amount, mismatches.status, mismatches.created_at, mismatches.updated_at, mismatches.mws_credential_id, mismatches.reimbursement_status, mismatches.special_alert, mismatches.payment_status, mismatches.viewed_at, mismatches.viewer_id, mismatches.response_labels, (COALESCE((SubPlan 2), (SubPlan 3)))
  • Sort Key: (COALESCE((SubPlan 2), (SubPlan 3))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=62190 read=1951767
3. 2.166 7,050.176 ↓ 7.4 229 1

Nested Loop (cost=1,156.73..308,152.85 rows=31 width=192) (actual time=64.404..7,050.176 rows=229 loops=1)

  • Output: mismatches.id, mismatches.mismatchable_type, mismatches.mismatchable_id, mismatches.amount, mismatches.status, mismatches.created_at, mismatches.updated_at, mismatches.mws_credential_id, mismatches.reimbursement_status, mismatches.special_alert, mismatches.payment_status, mismatches.viewed_at, mismatches.viewer_id, mismatches.response_labels, COALESCE((SubPlan 2), (SubPlan 3))
  • Buffers: shared hit=62187 read=1951767
4. 3.448 24.002 ↓ 3.8 229 1

Bitmap Heap Scan on public.mismatches (cost=1,156.30..3,132.76 rows=61 width=184) (actual time=20.688..24.002 rows=229 loops=1)

  • Output: mismatches.id, mismatches.mismatchable_type, mismatches.mismatchable_id, mismatches.amount, mismatches.status, mismatches.created_at, mismatches.updated_at, mismatches.mws_credential_id, mismatches.reimbursement_status, mismatches.special_alert, mismatches.payment_status, mismatches.viewed_at, mismatches.viewer_id, mismatches.response_labels
  • Recheck Cond: ((mismatches.mws_credential_id = 905) AND ((mismatches.status)::text = 'working'::text))
  • Filter: ('check_for_response'::text = ANY ((mismatches.response_labels)::text[]))
  • Rows Removed by Filter: 46
  • Heap Blocks: exact=316
  • Buffers: shared hit=534
5. 1.043 20.554 ↓ 0.0 0 1

BitmapAnd (cost=1,156.30..1,156.30 rows=621 width=0) (actual time=20.554..20.554 rows=0 loops=1)

  • Buffers: shared hit=218
6. 3.153 3.153 ↓ 1.1 10,025 1

Bitmap Index Scan on index_mismatches_on_mws_credential_id (cost=0.00..219.11 rows=9,425 width=0) (actual time=3.153..3.153 rows=10,025 loops=1)

  • Index Cond: (mismatches.mws_credential_id = 905)
  • Buffers: shared hit=39
7. 16.358 16.358 ↓ 1.4 51,649 1

Bitmap Index Scan on index_mismatches_on_status (cost=0.00..936.91 rows=37,931 width=0) (actual time=16.358..16.358 rows=51,649 loops=1)

  • Index Cond: ((mismatches.status)::text = 'working'::text)
  • Buffers: shared hit=179
8. 3.664 5.267 ↑ 1.0 1 229

Index Only Scan using mismatches_pkey on public.mismatches mismatches_1 (cost=0.42..16.88 rows=1 width=4) (actual time=0.020..0.023 rows=1 loops=229)

  • Output: mismatches_1.id
  • Index Cond: (mismatches_1.id = mismatches.id)
  • Filter: (alternatives: SubPlan 4 or hashed SubPlan 5)
  • Heap Fetches: 229
  • Buffers: shared hit=1832
9.          

SubPlan (forIndex Only Scan)

10. 1.603 1.603 ↑ 1.0 1 229

Index Only Scan using index_cases_on_mismatch_id on public.cases cases_2 (cost=0.42..8.44 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=229)

  • Index Cond: (cases_2.mismatch_id = mismatches_1.id)
  • Heap Fetches: 229
  • Buffers: shared hit=916
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.cases cases_3 (cost=0.00..2,454.88 rows=128,888 width=4) (never executed)

  • Output: cases_3.mismatch_id
12.          

SubPlan (forNested Loop)

13. 0.000 7,018.163 ↑ 1.0 1 229

Aggregate (cost=9,797.67..9,797.68 rows=1 width=8) (actual time=30.646..30.647 rows=1 loops=229)

  • Output: max(responses.time_posted)
  • Buffers: shared hit=59675 read=1951767
14.          

Initplan (forAggregate)

15. 0.687 4.351 ↑ 1.0 1 229

Limit (cost=8.45..8.45 rows=1 width=12) (actual time=0.019..0.019 rows=1 loops=229)

  • Output: cases.id, cases.created_at
  • Buffers: shared hit=1051
16. 2.519 3.664 ↑ 1.0 1 229

Sort (cost=8.45..8.45 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=229)

  • Output: cases.id, cases.created_at
  • Sort Key: cases.created_at DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1051
17. 1.145 1.145 ↓ 2.0 2 229

Index Scan using index_cases_on_mismatch_id on public.cases (cost=0.42..8.44 rows=1 width=12) (actual time=0.004..0.005 rows=2 loops=229)

  • Output: cases.id, cases.created_at
  • Index Cond: (mismatches_1.id = cases.mismatch_id)
  • Buffers: shared hit=1051
18. 7,015.186 7,015.186 ↑ 1.5 2 229

Seq Scan on public.responses (cost=0.00..9,789.21 rows=3 width=8) (actual time=23.610..30.634 rows=2 loops=229)

  • Output: responses.id, responses.case_id, responses.response_id, responses.sender, responses.template_id, responses.time_posted, responses.raw_message, responses.created_at, responses.updated_at
  • Filter: (responses.case_id = $1)
  • Rows Removed by Filter: 80815
  • Buffers: shared hit=59675 read=1951767
19. 0.136 0.578 ↑ 1.0 1 34

Aggregate (cost=8.44..8.45 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=34)

  • Output: max(cases_1.created_at)
  • Buffers: shared hit=146
20. 0.442 0.442 ↑ 1.0 1 34

Index Scan using index_cases_on_mismatch_id on public.cases cases_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=34)

  • Output: cases_1.created_at
  • Index Cond: (mismatches_1.id = cases_1.mismatch_id)
  • Buffers: shared hit=146
Planning time : 4.781 ms
Execution time : 7,051.681 ms