explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vMNr

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 8,222.628 ↑ 48.0 1 1

Limit (cost=638,998,918.79..638,998,921.79 rows=48 width=507) (actual time=8,222.626..8,222.628 rows=1 loops=1)

  • Buffers: shared hit=37,719 read=610
  • I/O Timings: read=2,648.896
2. 0.002 8,222.624 ↑ 48.0 1 1

Unique (cost=638,998,918.79..638,998,921.79 rows=48 width=507) (actual time=8,222.623..8,222.624 rows=1 loops=1)

  • Buffers: shared hit=37,719 read=610
  • I/O Timings: read=2,648.896
3. 0.154 8,222.622 ↑ 48.0 1 1

Sort (cost=638,998,918.79..638,998,918.91 rows=48 width=507) (actual time=8,222.621..8,222.622 rows=1 loops=1)

  • Sort Key: mohmapplic0_.received_date, mohmapplic0_.vsb_reference, mohmapplic0_.creation_timestamp, mohmapplic0_.creation_user, mohmapplic0_.modification_timestamp, mohmapplic0_.modification_user, mohmapplic0_.appointed_to_care_unit_employee, mohmapplic0_.assigned_zkc, mohmapplic0_.btc, mohmapplic0_.cancel_comment, mohmapplic0_.cancel_reason, mohmapplic0_.care_unit, mohmapplic0_.careprovider_reference, mohmapplic0_.dossier_id, mohmapplic0_.end_comment, mohmapplic0_.end_date, mohmapplic0_.end_reason, mohmapplic0_.max_delivery_date, mohmapplic0_.procedure, mohmapplic0_.provider_approval_number, mohmapplic0_.province, mohmapplic0_.sda_calculated, mohmapplic0_.zip_code, mohmapplic0_.zkc
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=37,719 read=610
  • I/O Timings: read=2,648.896
4. 2.755 8,222.468 ↑ 48.0 1 1

Nested Loop (cost=56,299.24..638,998,917.45 rows=48 width=507) (actual time=6,790.155..8,222.468 rows=1 loops=1)

  • Join Filter: ((mohmapplic0_.vsb_reference)::text = (applicatio1_.vsb_reference)::text)
  • Buffers: shared hit=37,705 read=610
  • I/O Timings: read=2,648.896
5. 2.623 6,121.618 ↑ 19.8 915 1

HashAggregate (cost=56,298.83..56,842.58 rows=18,125 width=18) (actual time=6,120.344..6,121.618 rows=915 loops=1)

  • Group Key: (mohmapplic2_.vsb_reference)::text
  • Buffers: shared hit=25,625 read=279
  • I/O Timings: read=839.105
6. 66.855 6,118.995 ↑ 19.8 915 1

Merge Join (cost=30,294.76..56,253.51 rows=18,125 width=18) (actual time=257.614..6,118.995 rows=915 loops=1)

  • Merge Cond: ((applicatio1_.vsb_reference)::text = (mohmapplic2_.vsb_reference)::text)
  • Buffers: shared hit=25,625 read=279
  • I/O Timings: read=839.105
7. 13.055 154.724 ↑ 19.8 915 1

Sort (cost=30,294.33..30,339.64 rows=18,125 width=9) (actual time=154.194..154.724 rows=915 loops=1)

  • Sort Key: applicatio1_.vsb_reference
  • Sort Method: quicksort Memory: 67kB
  • Buffers: shared hit=8,751
8. 141.669 141.669 ↑ 19.8 915 1

Seq Scan on application_status applicatio1_ (cost=0.00..29,012.38 rows=18,125 width=9) (actual time=4.795..141.669 rows=915 loops=1)

  • Filter: (is_current_status AND ((status)::text = 'UNDER_CONSIDERATION'::text))
  • Rows Removed by Filter: 622,412
  • Buffers: shared hit=8,751
9. 5,897.416 5,897.416 ↓ 1.0 124,521 1

Index Only Scan using application_pkey on application mohmapplic2_ (cost=0.42..25,013.66 rows=124,464 width=9) (actual time=0.066..5,897.416 rows=124,521 loops=1)

  • Heap Fetches: 11,323
  • Buffers: shared hit=16,874 read=279
  • I/O Timings: read=839.105
10. 1,948.594 2,098.095 ↓ 0.0 0 915

Index Scan using application_pkey on application mohmapplic0_ (cost=0.42..35,251.94 rows=1 width=507) (actual time=2.293..2.293 rows=0 loops=915)

  • Index Cond: ((vsb_reference)::text = (mohmapplic2_.vsb_reference)::text)
  • Filter: (sda_calculated AND ((application_type)::text = 'NEW_APPLICATION'::text) AND ((care_unit = 180) OR ((assigned_zkc = 180) AND (alternatives: SubPlan 1 or hashed SubPlan 2))))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=12,080 read=331
  • I/O Timings: read=1,809.791
11.          

SubPlan (for Index Scan)

12. 0.002 149.501 ↓ 0.0 0 1

Nested Loop (cost=0.42..35,250.63 rows=1 width=0) (actual time=149.501..149.501 rows=0 loops=1)

  • Buffers: shared hit=8,751
13. 149.499 149.499 ↓ 0.0 0 1

Seq Scan on application_status applicatio3_ (cost=0.00..35,246.65 rows=1 width=9) (actual time=149.499..149.499 rows=0 loops=1)

  • Filter: (is_current_status AND ((vsb_reference)::text = (mohmapplic0_.vsb_reference)::text) AND (((status)::character varying(255))::text = ANY ('{PASSED_TO_ZKC,ASKED_MORE_INFORMATION_ZKC,RECEIVED_MORE_INFORMATION_ZKC,HOMEVISIT_ZKC,CONFIRMED_ADVICE_ZKC,ADVICE_ZKC_RECEIVED}'::text[])))
  • Rows Removed by Filter: 623,327
  • Buffers: shared hit=8,751
14. 0.000 0.000 ↓ 0.0 0

Index Only Scan using application_pkey on application mohmapplic4_ (cost=0.42..3.95 rows=1 width=9) (never executed)

  • Index Cond: (vsb_reference = (mohmapplic0_.vsb_reference)::text)
  • Heap Fetches: 0
15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..45,502.62 rows=3,764 width=32) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on application_status applicatio3__1 (cost=0.00..33,688.08 rows=3,764 width=9) (never executed)

  • Filter: (is_current_status AND (((status)::character varying(255))::text = ANY ('{PASSED_TO_ZKC,ASKED_MORE_INFORMATION_ZKC,RECEIVED_MORE_INFORMATION_ZKC,HOMEVISIT_ZKC,CONFIRMED_ADVICE_ZKC,ADVICE_ZKC_RECEIVED}'::text[])))
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using application_pkey on application mohmapplic4__1 (cost=0.42..3.11 rows=1 width=9) (never executed)

  • Index Cond: (vsb_reference = (applicatio3__1.vsb_reference)::text)
  • Heap Fetches: 0
Planning time : 116.903 ms
Execution time : 8,243.904 ms