explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Icrk

Settings
# exclusive inclusive rows x rows loops node
1. 403.412 54,469.242 ↑ 1.0 1 1

Aggregate (cost=266,252.76..266,252.77 rows=1 width=96) (actual time=54,469.242..54,469.242 rows=1 loops=1)

2. 137.582 54,065.830 ↓ 1,238,419.0 1,238,419 1

Subquery Scan on sub (cost=266,250.16..266,252.74 rows=1 width=12) (actual time=51,684.953..54,065.830 rows=1,238,419 loops=1)

  • Filter: ((sub.version_no = sub.min_version_no) OR ((sub.version_no IS NULL) AND (sub.min_version_no IS NULL)))
3. 396.994 53,928.248 ↓ 14,400.2 1,238,419 1

WindowAgg (cost=266,250.16..266,251.66 rows=86 width=29) (actual time=51,684.945..53,928.248 rows=1,238,419 loops=1)

4. 3,371.941 53,531.254 ↓ 14,400.2 1,238,419 1

Sort (cost=266,250.16..266,250.37 rows=86 width=25) (actual time=51,684.908..53,531.254 rows=1,238,419 loops=1)

  • Sort Key: p.policy_ref
  • Sort Method: external merge Disk: 33,688kB
5. 1,067.386 50,159.313 ↓ 14,400.2 1,238,419 1

Nested Loop Left Join (cost=78.70..266,247.39 rows=86 width=25) (actual time=91.130..50,159.313 rows=1,238,419 loops=1)

  • Join Filter: (pv.creation_date > f.last_cle_creation_date)
  • Rows Removed by Join Filter: 1,231,412
6. 1,171.945 47,853.508 ↓ 14,400.2 1,238,419 1

Nested Loop (cost=78.28..265,778.94 rows=86 width=29) (actual time=85.769..47,853.508 rows=1,238,419 loops=1)

7. 384.337 31,820.223 ↓ 1,481.4 3,715,335 1

Nested Loop (cost=77.84..251,426.72 rows=2,508 width=46) (actual time=82.814..31,820.223 rows=3,715,335 loops=1)

8. 19.933 176.830 ↓ 149.6 95,302 1

Nested Loop (cost=77.28..6,372.56 rows=637 width=30) (actual time=82.426..176.830 rows=95,302 loops=1)

9. 2.033 2.033 ↑ 1.0 1 1

Seq Scan on br_pw_frame f (cost=0.00..19.74 rows=1 width=21) (actual time=2.030..2.033 rows=1 loops=1)

  • Filter: ((case_ref)::text = 'CA004237'::text)
  • Rows Removed by Filter: 622
10. 75.857 154.864 ↓ 29.7 95,302 1

Bitmap Heap Scan on br_pw_policy po (cost=77.28..6,320.75 rows=3,207 width=17) (actual time=80.390..154.864 rows=95,302 loops=1)

  • Recheck Cond: (pw_frame_id = f.id)
  • Heap Blocks: exact=2,227
11. 79.007 79.007 ↓ 29.7 95,302 1

Bitmap Index Scan on br_pw_policy_uk (cost=0.00..76.47 rows=3,207 width=0) (actual time=79.007..79.007 rows=95,302 loops=1)

  • Index Cond: (pw_frame_id = f.id)
12. 31,259.056 31,259.056 ↓ 3.5 39 95,302

Index Scan using idx_fi_premiums_policy_ref on fi_premiums p (cost=0.56..384.59 rows=11 width=25) (actual time=0.025..0.328 rows=39 loops=95,302)

  • Index Cond: ((policy_ref)::text = (po.policy_ref)::text)
  • Filter: ((period_from >= '2019-09-01 00:00:00'::timestamp without time zone) AND (period_from < COALESCE(period_to)))
  • Rows Removed by Filter: 102
13. 14,861.340 14,861.340 ↓ 0.0 0 3,715,335

Index Scan using idx_fi_ae_premiums_ext_premium_id on fi_ae_premiums_ext pe (cost=0.44..5.71 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=3,715,335)

  • Index Cond: (premium_id = p.id)
  • Filter: ((case_ref)::text = 'CA004237'::text)
  • Rows Removed by Filter: 1
14. 1,238.419 1,238.419 ↑ 1.0 1 1,238,419

Index Scan using br_pw_policy_version_uk on br_pw_policy_version pv (cost=0.42..5.43 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=1,238,419)

  • Index Cond: (pw_policy_id = po.id)
  • Filter: ((transaction_code)::text = ANY ('{NEW,DEL}'::text[]))
  • Rows Removed by Filter: 0
Planning time : 53.011 ms
Execution time : 54,476.489 ms