explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lv5L : Test

Settings
# exclusive inclusive rows x rows loops node
1. 2.887 505,549.557 ↑ 1.0 1 1

Aggregate (cost=336,895,620.10..336,895,620.11 rows=1 width=8) (actual time=505,549.557..505,549.557 rows=1 loops=1)

2. 240.014 505,546.670 ↑ 24.7 3,518 1

Seq Scan on pending_case (cost=0.00..336,895,403.15 rows=86,778 width=33) (actual time=384.481..505,546.670 rows=3,518 loops=1)

  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 165,594
3.          

SubPlan (for Seq Scan)

4. 169.112 505,306.656 ↓ 0.0 0 169,112

HashAggregate (cost=1,941.06..1,941.12 rows=6 width=4) (actual time=2.988..2.988 rows=0 loops=169,112)

  • Group Key: (1)
5. 0.000 505,137.544 ↓ 0.0 0 169,112

Append (cost=6.95..1,941.05 rows=6 width=4) (actual time=2.972..2.987 rows=0 loops=169,112)

6. 102.834 8,962.936 ↓ 0.0 0 169,112

Nested Loop (cost=6.95..84.00 rows=4 width=4) (actual time=0.053..0.053 rows=0 loops=169,112)

7. 631.883 4,396.912 ↑ 2.0 1 169,112

Nested Loop (cost=4.85..29.19 rows=2 width=33) (actual time=0.022..0.026 rows=1 loops=169,112)

8. 338.224 2,029.344 ↑ 2.0 1 169,112

Bitmap Heap Scan on pending_case_agent pca (cost=4.44..12.32 rows=2 width=66) (actual time=0.012..0.012 rows=1 loops=169,112)

  • Recheck Cond: (pend_rec_id = pending_case.pend_rec_id)
  • Heap Blocks: exact=216,560
9. 1,691.120 1,691.120 ↑ 2.0 1 169,112

Bitmap Index Scan on pending_case_agent_idx_001 (cost=0.00..4.43 rows=2 width=0) (actual time=0.010..0.010 rows=1 loops=169,112)

  • Index Cond: (pend_rec_id = pending_case.pend_rec_id)
10. 1,735.685 1,735.685 ↑ 1.0 1 247,955

Index Only Scan using pk_agent on agent a (cost=0.42..8.44 rows=1 width=33) (actual time=0.007..0.007 rows=1 loops=247,955)

  • Index Cond: (agent_rec_id = pca.agent_rec_id)
  • Heap Fetches: 247,955
11. 101.300 4,463.190 ↓ 0.0 0 247,955

Materialize (cost=2.09..54.77 rows=2 width=33) (actual time=0.018..0.018 rows=0 loops=247,955)

12. 0.000 4,361.890 ↓ 0.0 0 167,765

Nested Loop (cost=2.09..54.76 rows=2 width=33) (actual time=0.026..0.026 rows=0 loops=167,765)

13. 164.828 4,361.890 ↓ 0.0 0 167,765

Nested Loop (cost=1.67..37.88 rows=2 width=66) (actual time=0.026..0.026 rows=0 loops=167,765)

14. 0.000 4,194.125 ↓ 0.0 0 167,765

Nested Loop (cost=1.25..25.41 rows=1 width=33) (actual time=0.025..0.025 rows=0 loops=167,765)

15. 340.723 4,194.125 ↓ 0.0 0 167,765

Nested Loop (cost=0.83..16.97 rows=1 width=66) (actual time=0.025..0.025 rows=0 loops=167,765)

16. 3,523.065 3,523.065 ↓ 0.0 0 167,765

Index Scan using pk_pending_case on pending_case pc (cost=0.42..8.53 rows=1 width=33) (actual time=0.021..0.021 rows=0 loops=167,765)

  • Index Cond: (pend_rec_id = pending_case.pend_rec_id)
  • Filter: ((pending.nbcsf_policy_retention_reqflag(cntrct_nbr, status_cde, (status_chg_dte)::date, outstanding_req, 0) = 1) AND
  • ((status_chg_dte >= (CURRENT_DATE - 10)) OR (upper((status_desc)::text) <> 'ISSUED'::text) OR
  • (upper((cntrct_type)::text) <> 'ANNUITY'::text)) AND ((status_chg_dte >= (CURRENT_DATE - 90)) OR
  • (upper((status_desc)::text) <> 'POLCHG-QUOTE'::text) OR (upper((cntrct_type)::text) <> 'LIFE'::text)) AND
  • ((status_chg_dte >= (CURRENT_DATE - 244)) OR ((upper((status_desc)::text) <> 'POLCHG-PENDING'::text) AND
  • (upper((status_desc)::text) <> 'POLCHG-APPROVED'::text)) OR (upper((cntrct_type)::text) <> 'LIFE'::text)) AND
  • ((status_chg_dte >= (CURRENT_DATE - 30)) OR ((upper((status_desc)::text) <> 'POLCHG-DECLINED'::text) AND
  • (upper((status_desc)::text) <> 'POLCHG-CANCELLED'::text)) OR (upper((cntrct_type)::text) <>'LIFE'::text)))
  • Rows Removed by Filter: 1
17. 330.337 330.337 ↓ 0.0 0 47,191

Index Scan using pend_case_bdfa_idx001 on pending_case_bdfa pcbdfa (cost=0.41..8.43 rows=1 width=66) (actual time=0.007..0.007 rows=0 loops=47,191)

  • Index Cond: (pend_rec_id = pending_case.pend_rec_id)
18. 56.910 56.910 ↓ 0.0 0 5,691

Index Scan using bdfa_rec_idpk on bdfa (cost=0.42..8.44 rows=1 width=33) (actual time=0.010..0.010 rows=0 loops=5,691)

  • Index Cond: (bdfa_rec_id = pcbdfa.bdfa_rec_id)
  • Filter: ((ssn_tin_id)::text = ANY ('{ssn1,ssn2,ssn3}'::text[]))
  • Rows Removed by Filter: 1
19. 2.937 2.937 ↑ 2.0 1 267

Index Scan using pending_case_client_idx_001 on pending_case_client pcc (cost=0.42..12.45 rows=2 width=66) (actual time=0.011..0.011 rows=1 loops=267)

  • Index Cond: (pend_rec_id = pending_case.pend_rec_id)
20. 2.810 2.810 ↑ 1.0 1 281

Index Only Scan using pk_client on client c (cost=0.42..8.44 rows=1 width=33) (actual time=0.010..0.010 rows=1 loops=281)

  • Index Cond: (client_rec_id = pcc.client_rec_id)
  • Heap Fetches: 281
21. 111.302 496,174.608 ↓ 0.0 0 169,112

Nested Loop (cost=14.02..1,856.99 rows=2 width=4) (actual time=2.923..2.934 rows=0 loops=169,112)

22. 129.584 496,005.496 ↓ 0.0 0 169,112

Nested Loop (cost=13.60..1,840.11 rows=2 width=33) (actual time=2.922..2.933 rows=0 loops=169,112)

23. 0.000 495,836.384 ↓ 0.0 0 169,112

Nested Loop (cost=13.18..1,827.64 rows=1 width=33) (actual time=2.921..2.932 rows=0 loops=169,112)

  • Join Filter: (pca_1.agent_rec_id = a_1.agent_rec_id)
  • -> Index Only Scan using pk_agent on agent a_1 (cost=0.42..0.67 rows=1 width=33) (actual time=0.008..0.008rows=1 loops=3,294)
24. 266.832 495,836.384 ↓ 0.0 0 169,112

Nested Loop (cost=12.76..1,826.96 rows=1 width=99) (actual time=2.921..2.932 rows=0 loops=169,112)

  • Index Cond: (agent_rec_id = (cache.agent_rec_id)::bpchar)
  • Heap Fetches: 3,294
25. 84,459.093 495,329.048 ↓ 0.0 0 169,112

Hash Join (cost=12.34..1,818.42 rows=1 width=99) (actual time=2.892..2.929 rows=0 loops=169,112)

  • Hash Cond: ((cache.agent_rec_id)::bpchar = pca_1.agent_rec_id)
26. 409,178.835 409,178.835 ↑ 9.4 5,479 167,765

Seq Scan on cache_agent_rec_ids cache (cost=0.00..1,547.85 rows=51,644 width=33) (actual time=1.876..2.439 rows=5,479 loops=167,765)

  • Filter: ((user_id)::text = 'KCEJ1'::text)
  • Rows Removed by Filter: 2,396
27. 338.224 1,691.120 ↑ 2.0 1 169,112

Hash (cost=12.32..12.32 rows=2 width=66) (actual time=0.010..0.010 rows=1 loops=169,112)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 169.112 1,352.896 ↑ 2.0 1 169,112

Bitmap Heap Scan on pending_case_agent pca_1 (cost=4.44..12.32 rows=2 width=66) (actual time=0.008..0.008 rows=1 loops=169,112)

  • Recheck Cond: (pend_rec_id = pending_case.pend_rec_id)
  • Heap Blocks: exact=216,560
29. 1,183.784 1,183.784 ↑ 2.0 1 169,112

Bitmap Index Scan on pending_case_agent_idx_001 (cost=0.00..4.43 rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=169,112)

  • Index Cond: (pend_rec_id = pending_case.pend_rec_id)
30. 240.504 240.504 ↓ 0.0 0 10,932

Index Scan using pk_pending_case on pending_case pc_1 (cost=0.42..8.53 rows=1 width=33) (actual time=0.022..0.022 rows=0 loops=10,932)

  • Index Cond: (pend_rec_id = pending_case.pend_rec_id)
  • Filter: ((pending.nbcsf_policy_retention_reqflag(cntrct_nbr, status_cde, (status_chg_dte)::date,outstanding_req, 0) = 1) AND
  • ((status_chg_dte >= (CURRENT_DATE - 10)) OR (upper((status_desc)::text) <> 'ISSUED'::text) OR (upper((cntrct_type)::text) <> 'ANNUITY'::text))
  • AND ((status_chg_dte >= (CURRENT_DATE - 90)) OR (upper((status_desc)::text) <> 'POLCHG-QUOTE'::text) OR
  • (upper((cntrct_type)::text) <> 'LIFE'::text)) AND ((status_chg_dte >= (CURRENT_DATE - 244)) OR
  • ((upper((status_desc)::text) <> 'POLCHG-PENDING'::text) AND (upper((status_desc)::text) <> 'POLCHG-APPROVED'::text)) OR
  • (upper((cntrct_type)::text) <> 'LIFE'::text)) AND ((status_chg_dte >= (CURRENT_DATE - 30)) OR
  • ((upper((status_desc)::text) <> 'POLCHG-DECLINED'::text) AND (upper((status_desc)::text) <> 'POLCHG-CANCELLED'::text)) OR
  • (upper((cntrct_type)::text) <> 'LIFE'::text)))
  • Rows Removed by Filter: 1
31. 39.528 39.528 ↑ 1.0 2 3,294

Index Scan using pending_case_client_idx_001 on pending_case_client pcc_1 (cost=0.42..12.45 rows=2 width=66) (actual time=0.011..0.012 rows=2 loops=3,294)

  • Index Cond: (pend_rec_id = pending_case.pend_rec_id)
32. 57.810 57.810 ↑ 1.0 1 5,781

Index Only Scan using pk_client on client c_1 (cost=0.42..8.44 rows=1 width=33) (actual time=0.010..0.010 rows=1 loops=5,781)

  • Index Cond: (client_rec_id = pcc_1.client_rec_id)
  • Heap Fetches: 5,781
Planning time : 4.721 ms
Execution time : 505,550.835 ms