explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CBiQ : CASE DETAILS

Settings
# exclusive inclusive rows x rows loops node
1. 77,572.698 139,311.328 ↓ 757.8 6,820 1

Nested Loop Left Join (cost=15,519.35..24,894.47 rows=9 width=68) (actual time=308.477..139,311.328 rows=6,820 loops=1)

  • Join Filter: (cfdl.case_id = c.case_id)
  • Rows Removed by Join Filter: 310152069
2. 15.555 154.030 ↓ 757.8 6,820 1

Nested Loop (cost=139.75..7,926.51 rows=9 width=44) (actual time=12.362..154.030 rows=6,820 loops=1)

3. 18.957 70.275 ↓ 757.8 6,820 1

Hash Join (cost=139.47..7,923.62 rows=9 width=116) (actual time=12.343..70.275 rows=6,820 loops=1)

  • Hash Cond: ((c.workflow_id = ws.workflow_id) AND (c.status_id = ws.status_id))
4. 49.691 49.691 ↓ 1.3 6,820 1

Seq Scan on enterprise_cases_archive c (cost=0.00..7,745.28 rows=5,171 width=116) (actual time=10.699..49.691 rows=6,820 loops=1)

  • Filter: ((created_at >= '1543640400000'::bigint) AND (created_at <= '1546405199000'::bigint) AND (enterprise_id = '2005cf34-a4e1-43d0-addf-62cae740526c'::text))
  • Rows Removed by Filter: 80476
5. 0.866 1.627 ↓ 1.0 1,831 1

Hash (cost=112.19..112.19 rows=1,819 width=72) (actual time=1.627..1.627 rows=1,831 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 206kB
6. 0.761 0.761 ↓ 1.0 1,831 1

Seq Scan on enterprise_workflows_status ws (cost=0.00..112.19 rows=1,819 width=72) (actual time=0.006..0.761 rows=1,831 loops=1)

7. 68.200 68.200 ↑ 1.0 1 6,820

Index Only Scan using enterprise_workflows_pkey on enterprise_workflows w (cost=0.28..0.31 rows=1 width=36) (actual time=0.008..0.010 rows=1 loops=6,820)

  • Index Cond: (workflow_id = c.workflow_id)
  • Heap Fetches: 6820
8. 61,282.684 61,584.600 ↓ 203.9 45,477 6,820

Materialize (cost=15,379.60..16,938.32 rows=223 width=37) (actual time=0.033..9.030 rows=45,477 loops=6,820)

9. 20.233 301.916 ↓ 203.9 45,477 1

Subquery Scan on cfdl (cost=15,379.60..16,937.20 rows=223 width=37) (actual time=220.168..301.916 rows=45,477 loops=1)

  • Filter: (cfdl.row_num = 1)
  • Rows Removed by Filter: 14
10. 39.792 281.683 ↓ 1.0 45,491 1

WindowAgg (cost=15,379.60..16,380.91 rows=44,503 width=97) (actual time=220.157..281.683 rows=45,491 loops=1)

11. 206.719 241.891 ↓ 1.0 45,491 1

Sort (cost=15,379.60..15,490.85 rows=44,503 width=57) (actual time=220.149..241.891 rows=45,491 loops=1)

  • Sort Key: enterprise_cases_form_data_archive.case_id, enterprise_cases_form_data_archive.created_at
  • Sort Method: external merge Disk: 3288kB
12. 30.374 35.172 ↓ 1.0 45,491 1

Bitmap Heap Scan on enterprise_cases_form_data_archive (cost=1,833.32..11,943.61 rows=44,503 width=57) (actual time=6.193..35.172 rows=45,491 loops=1)

  • Recheck Cond: (field_name = 'patientLastName'::text)
  • Heap Blocks: exact=8913
13. 4.798 4.798 ↓ 1.0 45,685 1

Bitmap Index Scan on ix_enterprise_cases_form_data_archive_field_name (cost=0.00..1,822.19 rows=44,503 width=0) (actual time=4.798..4.798 rows=45,685 loops=1)

  • Index Cond: (field_name = 'patientLastName'::text)