---

Nested Loop Anti Join  (cost=57795.73..1814420.12 rows=121510 width=8) (actual rows=743050 loops=1)
  Join Filter: (o.effective_date <= p.report_date)
  Buffers: shared hit=11371553 read=112581, temp written=2726
  I/O Timings: shared read=72000.970, temp write=32.525
  CTE latest_death_info
    ->  Result  (cost=0.42..57790.33 rows=634009 width=16) (actual rows=859073 loops=1)
          Buffers: shared hit=874964 read=1840
          I/O Timings: shared read=41.224
          ->  Unique  (cost=0.42..57790.33 rows=634009 width=16) (actual rows=859073 loops=1)
                Buffers: shared hit=874964 read=1840
                I/O Timings: shared read=41.224
                ->  Index Scan using adi_last_by_individual_idx on application_death_info a  (cost=0.42..55594.82 rows=878205 width=16) (actual rows=874180 loops=1)
                      Buffers: shared hit=874964 read=1840
                      I/O Timings: shared read=41.224
  InitPlan 2
    ->  Result  (cost=0.00..0.01 rows=1 width=1) (actual rows=1 loops=1)
  InitPlan 4
    ->  Result  (cost=1.02..1.03 rows=1 width=1) (never executed)
          InitPlan 3
            ->  Seq Scan on user_group ug  (cost=0.00..1.02 rows=1 width=0) (never executed)
                  Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
  ->  Nested Loop  (cost=2.93..923462.50 rows=131961 width=12) (actual rows=809066 loops=1)
        Buffers: shared hit=8378847 read=17494, temp written=2726
        I/O Timings: shared read=395.631, temp write=32.525
        ->  Nested Loop  (cost=0.43..211876.92 rows=277417 width=16) (actual rows=989368 loops=1)
              Buffers: shared hit=4421379 read=17494, temp written=2726
              I/O Timings: shared read=395.631, temp write=32.525
              ->  Nested Loop  (cost=0.00..20606.30 rows=211336 width=12) (actual rows=859072 loops=1)
                    Join Filter: (death_info.death_date <= p.report_date)
                    Rows Removed by Join Filter: 1
                    Buffers: shared hit=874964 read=1841, temp written=2726
                    I/O Timings: shared read=41.598, temp write=32.525
                    ->  Seq Scan on dia_param p  (cost=0.00..1.01 rows=1 width=4) (actual rows=1 loops=1)
                          Buffers: shared read=1
                          I/O Timings: shared read=0.374
                    ->  CTE Scan on latest_death_info death_info  (cost=0.00..12680.18 rows=634009 width=8) (actual rows=859073 loops=1)
                          Buffers: shared hit=874964 read=1840, temp written=2726
                          I/O Timings: shared read=41.224, temp write=32.525
              ->  Index Scan using sharer_individual_id_idx on sharer s  (cost=0.43..0.90 rows=1 width=12) (actual rows=1 loops=859072)
                    Index Cond: (individual_id = death_info.individual_id)
                    Buffers: shared hit=3546415 read=15653
                    I/O Timings: shared read=354.033
        ->  Index Scan using contract_pkey on contract c  (cost=2.50..2.57 rows=1 width=4) (actual rows=1 loops=989368)
              Index Cond: (id = s.contract_id)
              Filter: (((service_type)::text = 'OPS'::text) AND CASE WHEN (InitPlan 2).col1 THEN true WHEN (InitPlan 4).col1 THEN (ANY (id = (hashed SubPlan 5).col1)) ELSE (NOT (ANY (id = (hashed SubPlan 6).col1))) END)
              Rows Removed by Filter: 0
              Buffers: shared hit=3957468
              SubPlan 5
                ->  Nested Loop  (cost=0.00..1.03 rows=1 width=8) (never executed)
                      Join Filter: (ug_1.group_id = a_1.group_id)
                      ->  Seq Scan on user_group ug_1  (cost=0.00..1.02 rows=1 width=8) (never executed)
                            Filter: (user_id = (NULLIF(current_setting('app.user_id'::text, true), ''::text))::bigint)
                      ->  Seq Scan on rbac_contract_access a_1  (cost=0.00..0.00 rows=1 width=16) (never executed)
              SubPlan 6
                ->  Nested Loop  (cost=0.00..1.02 rows=1 width=8) (never executed)
                      Join Filter: (a_2.group_id = g.id)
                      ->  Seq Scan on rbac_contract_access a_2  (cost=0.00..0.00 rows=1 width=16) (never executed)
                      ->  Seq Scan on individual_groups g  (cost=0.00..1.01 rows=1 width=4) (never executed)
                            Filter: hidden_from_external
  ->  Nested Loop  (cost=1.43..3.09 rows=291 width=8) (actual rows=0 loops=809066)
        Buffers: shared hit=2992706 read=95087
        I/O Timings: shared read=71605.339
        ->  Nested Loop  (cost=1.00..2.20 rows=2 width=16) (actual rows=0 loops=809066)
              Buffers: shared hit=2740703 read=83026
              I/O Timings: shared read=62598.096
              ->  Index Scan using ops_transfer_details_sharer_id on ops_transfer_details d  (cost=0.43..0.48 rows=2 width=8) (actual rows=0 loops=809066)
                    Index Cond: (sharer_id = s.id)
                    Buffers: shared hit=2421389 read=71892
                    I/O Timings: shared read=55332.421
              ->  Index Scan using idx_operation_transfer_register_id on operation o  (cost=0.57..0.85 rows=1 width=8) (actual rows=1 loops=66108)
                    Index Cond: (ops_transfer_register_id = d.ops_transfer_register_id)
                    Filter: (approved IS NOT NULL)
                    Buffers: shared hit=319314 read=11134
                    I/O Timings: shared read=7265.675
        ->  Index Only Scan using ops_transfer_register_pk on ops_transfer_register tr  (cost=0.42..0.45 rows=1 width=4) (actual rows=1 loops=66016)
              Index Cond: (id = d.ops_transfer_register_id)
              Heap Fetches: 66016
              Buffers: shared hit=252003 read=12061
              I/O Timings: shared read=9007.243
Planning:
  Buffers: shared hit=89
Planning Time: 3.377 ms
Execution Time: 96341.292 ms