explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9z2w

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 9,883.695 ↑ 48.0 1 1

Limit (cost=638,677,057.36..638,677,060.36 rows=48 width=507) (actual time=9,883.694..9,883.695 rows=1 loops=1)

  • Buffers: shared hit=21,959 read=15,884 written=3
  • I/O Timings: read=9,254.007 write=0.090
2. 0.005 9,883.693 ↑ 48.0 1 1

Unique (cost=638,677,057.36..638,677,060.36 rows=48 width=507) (actual time=9,883.693..9,883.693 rows=1 loops=1)

  • Buffers: shared hit=21,959 read=15,884 written=3
  • I/O Timings: read=9,254.007 write=0.090
3. 0.079 9,883.688 ↑ 48.0 1 1

Sort (cost=638,677,057.36..638,677,057.48 rows=48 width=507) (actual time=9,883.688..9,883.688 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=21,959 read=15,884 written=3
  • I/O Timings: read=9,254.007 write=0.090
4. 1.676 9,883.609 ↑ 48.0 1 1

Nested Loop (cost=56,292.05..638,677,056.02 rows=48 width=507) (actual time=8,191.342..9,883.609 rows=1 loops=1)

  • Join Filter: ((mohmapplic0_.vsb_reference)::text = (applicatio1_.vsb_reference)::text)
  • Buffers: shared hit=21,959 read=15,884 written=3
  • I/O Timings: read=9,254.007 write=0.090
5. 3.184 7,906.133 ↑ 19.6 925 1

HashAggregate (cost=56,291.63..56,835.23 rows=18,120 width=18) (actual time=7,905.111..7,906.133 rows=925 loops=1)

  • Group Key: (mohmapplic2_.vsb_reference)::text
  • Buffers: shared hit=19,264 read=6,130
  • I/O Timings: read=7,581.142
6. 51.255 7,902.949 ↑ 19.6 925 1

Merge Join (cost=30,287.75..56,246.33 rows=18,120 width=18) (actual time=179.559..7,902.949 rows=925 loops=1)

  • Merge Cond: ((applicatio1_.vsb_reference)::text = (mohmapplic2_.vsb_reference)::text)
  • Buffers: shared hit=19,264 read=6,130
  • I/O Timings: read=7,581.142
7. 17.745 131.471 ↑ 19.6 925 1

Sort (cost=30,287.33..30,332.63 rows=18,120 width=9) (actual time=130.680..131.471 rows=925 loops=1)

  • Sort Key: applicatio1_.vsb_reference
  • Sort Method: quicksort Memory: 68kB
  • Buffers: shared hit=8,749
8. 113.726 113.726 ↑ 19.6 925 1

Seq Scan on application_status applicatio1_ (cost=0.00..29,005.76 rows=18,120 width=9) (actual time=3.294..113.726 rows=925 loops=1)

  • Filter: (is_current_status AND ((status)::text = 'UNDER_CONSIDERATION'::text))
  • Rows Removed by Filter: 622,265
  • Buffers: shared hit=8,749
9. 7,720.223 7,720.223 ↓ 1.0 124,506 1

Index Only Scan using application_pkey on application mohmapplic2_ (cost=0.42..25,013.66 rows=124,464 width=9) (actual time=10.229..7,720.223 rows=124,506 loops=1)

  • Heap Fetches: 10,812
  • Buffers: shared hit=10,515 read=6,130
  • I/O Timings: read=7,581.142
10. 1,669.460 1,975.800 ↓ 0.0 0 925

Index Scan using application_pkey on application mohmapplic0_ (cost=0.42..35,243.91 rows=1 width=507) (actual time=2.136..2.136 rows=0 loops=925)

  • 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=2,695 read=9,754 written=3
  • I/O Timings: read=1,672.865 write=0.090
11.          

SubPlan (for Index Scan)

12. 0.002 306.340 ↓ 0.0 0 1

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

  • Buffers: shared hit=1 read=8,748 written=3
  • I/O Timings: read=71.901 write=0.090
13. 306.338 306.338 ↓ 0.0 0 1

Seq Scan on application_status applicatio3_ (cost=0.00..35,238.61 rows=1 width=9) (actual time=306.338..306.338 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,190
  • Buffers: shared hit=1 read=8,748 written=3
  • I/O Timings: read=71.901 write=0.090
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,494.94 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,680.40 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 : 2.533 ms
Execution time : 9,884.235 ms