explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m8Xs

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 13,265.980 ↓ 10.0 10 1

Limit (cost=24,034.83..24,093.84 rows=1 width=296) (actual time=13,265.814..13,265.980 rows=10 loops=1)

2. 0.022 13,265.977 ↓ 10.0 10 1

Result (cost=24,034.83..24,093.84 rows=1 width=296) (actual time=13,265.811..13,265.977 rows=10 loops=1)

3. 0.040 13,265.755 ↓ 10.0 10 1

Sort (cost=24,034.83..24,034.83 rows=1 width=232) (actual time=13,265.753..13,265.755 rows=10 loops=1)

  • Sort Key: (CASE WHEN (pcy2_.mandatory_policy IS TRUE) THEN ((((pcy2_.dko_oc)::date + '1 day'::interval))::date - ('now'::cstring)::date) ELSE CASE WHEN ((pcy2_.mandatory_policy IS FALSE) OR (pcy2_.mandatory_policy IS NULL)) THEN ((((pcy2_.protection_end_date)::date + '30 days'::interval))::date - ('now'::cstring)::date) ELSE '-1'::integer END END), pcy2_.modification_date DESC
  • Sort Method: quicksort Memory: 29kB
4. 0.047 13,265.715 ↓ 18.0 18 1

Nested Loop (cost=2.42..24,034.82 rows=1 width=232) (actual time=13,217.608..13,265.715 rows=18 loops=1)

5. 0.010 13,265.578 ↓ 18.0 18 1

Nested Loop Left Join (cost=2.00..24,034.29 rows=1 width=192) (actual time=13,217.586..13,265.578 rows=18 loops=1)

6. 43.006 13,265.478 ↓ 18.0 18 1

Nested Loop (cost=1.57..24,026.58 rows=1 width=191) (actual time=13,217.571..13,265.478 rows=18 loops=1)

  • Join Filter: ((pcy2_.product_id = prd4_.id) AND (((prd4_.product_class = 'CP'::text) AND ((pcy2_.user_login)::text = '082949'::text)) OR ((prd4_.product_class = 'CC'::text) AND (((pcy2_.unit)::text = '052935'::text) OR ((pcy2_.user_login)::text = '082949'::text))) OR ((prd4_.product_class = 'CP'::text) AND ((pcy2_.owner_user_login)::text = '082949'::text)) OR ((prd4_.product_class = 'CC'::text) AND (((pcy2_.owner_unit)::text = '052935'::text) OR ((pcy2_.owner_user_login)::text = '082949'::text)))) AND CASE WHEN (pcy2_.mandatory_policy IS TRUE) THEN ((('now'::cstring)::date < ((pcy2_.dko_oc)::date + '1 day'::interval)) AND ((((LEAST(pcy2_.protection_start_date, pcy2_.dpo_oc))::date + '1 year'::interval) - (((prd4_.days_to_policy_renewal)::text || ' days'::text))::interval) <= ('now'::cstring)::date)) ELSE CASE WHEN ((pcy2_.mandatory_policy IS FALSE) OR (pcy2_.mandatory_policy IS NULL)) THEN ((('now'::cstring)::date >= (((pcy2_.protection_start_date)::date + '1 year'::interval) - (((prd4_.days_to_policy_renewal)::text || ' days'::text))::interval)) AND ((((pcy2_.protection_start_date)::date + '1 year'::interval) + '30 days'::interval) >= ('now'::cstring)::date)) ELSE false END END)
  • Rows Removed by Join Filter: 231332
7. 0.122 0.122 ↓ 2.4 50 1

Seq Scan on prd_product prd4_ (cost=0.00..13.29 rows=21 width=34) (actual time=0.019..0.122 rows=50 loops=1)

  • Filter: ((days_to_policy_renewal <> '-1'::integer) AND ((code)::text = ANY ('{Agro_GM14,rubiAgroEkspert,AutoAssist,AutoAssistDirect,Biznes_Cesja,A15,A15_BGZ,ConcordiaBiznes,ConcordiaDOM,CMD,CP,CBIZwierzeta,DSZ-BB1,MGR,OCBUP,OCCERT,OCPosrednikow,OP,DSZ,SASS,GRKBIndSam}'::text[])))
  • Rows Removed by Filter: 138
8. 12.258 13,222.350 ↓ 257.1 4,627 50

Materialize (cost=1.57..23,963.26 rows=18 width=201) (actual time=29.950..264.447 rows=4,627 loops=50)

9. 2,413.371 13,210.092 ↓ 257.1 4,627 1

Nested Loop (cost=1.57..23,963.17 rows=18 width=201) (actual time=1,497.504..13,210.092 rows=4,627 loops=1)

10. 435.331 3,431.969 ↓ 183.4 3,682,376 1

Nested Loop (cost=1.14..12,973.26 rows=20,073 width=16) (actual time=26.977..3,431.969 rows=3,682,376 loops=1)

11. 0.303 84.436 ↓ 8.9 186 1

Nested Loop (cost=0.70..1,163.41 rows=21 width=16) (actual time=14.302..84.436 rows=186 loops=1)

12. 83.203 83.203 ↑ 1.0 186 1

Index Scan using idx_product_item_alias on prd_item g3_1_ (cost=0.42..725.29 rows=186 width=8) (actual time=14.278..83.203 rows=186 loops=1)

  • Index Cond: ((alias)::text = 'UBEZPIECZAJACY'::text)
13. 0.930 0.930 ↑ 1.0 1 186

Index Only Scan using pk_prd_group on prd_group g3_ (cost=0.29..2.35 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=186)

  • Index Cond: (id = g3_1_.id)
  • Heap Fetches: 186
14. 2,912.202 2,912.202 ↓ 1.1 19,798 186

Index Scan using idx_group_id on pcy_person_value this_ (cost=0.43..375.44 rows=18,693 width=24) (actual time=0.449..15.657 rows=19,798 loops=186)

  • Index Cond: (group_id = g3_.id)
15. 7,364.752 7,364.752 ↓ 0.0 0 3,682,376

Index Scan using pk_pcy_policy on pcy_policy pcy2_ (cost=0.43..0.54 rows=1 width=185) (actual time=0.002..0.002 rows=0 loops=3,682,376)

  • Index Cond: (id = this_.policy_id)
  • Filter: (actual AND ((subtype)::text <> 'RENEWED'::text) AND ((status)::text <> ALL ('{CANCELED,CANCELED_BY_HQ}'::text[])) AND (((type)::text = 'POLICY'::text) OR ((type)::text = 'REQUEST'::text)) AND (((user_login)::text = '082949'::text) OR ((unit)::text = '052935'::text) OR ((user_login)::text = '082949'::text) OR ((owner_user_login)::text = '082949'::text) OR ((owner_unit)::text = '052935'::text) OR ((owner_user_login)::text = '082949'::text)))
  • Rows Removed by Filter: 1
16. 0.090 0.090 ↑ 1.0 1 18

Index Scan using pk_pcy_policy_additional on pcy_policy_additional pcy2_1_ (cost=0.43..7.69 rows=1 width=9) (actual time=0.005..0.005 rows=1 loops=18)

  • Index Cond: (pcy2_.id = id)
17. 0.090 0.090 ↑ 1.0 1 18

Index Scan using pk_prs_person on prs_person prs1_ (cost=0.43..0.48 rows=1 width=77) (actual time=0.005..0.005 rows=1 loops=18)

  • Index Cond: (id = this_.person_id)
18.          

SubPlan (for Result)

19. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_prs_address on prs_address address (cost=0.43..8.47 rows=1 width=32) (never executed)

  • Index Cond: (id = prs1_.address_addt)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_prs_address on prs_address address_1 (cost=0.43..8.47 rows=1 width=32) (never executed)

  • Index Cond: (id = prs1_.address_perm)
21. 0.080 0.080 ↑ 1.0 1 10

Index Scan using pk_prs_address on prs_address address_2 (cost=0.43..8.47 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=10)

  • Index Cond: (id = prs1_.address_corr)
22. 0.020 0.100 ↑ 1.0 1 10

Nested Loop (cost=0.72..16.77 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=10)

23. 0.040 0.040 ↑ 1.0 1 10

Index Scan using pk_pcy_policy on pcy_policy p (cost=0.43..8.45 rows=1 width=7) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (id = this_.policy_id)
24. 0.040 0.040 ↑ 1.0 1 10

Index Scan using uq_usr_user_login on usr_user u (cost=0.29..8.31 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: ((login)::text = (p.user_login)::text)
  • Filter: ((jsta IS NOT NULL) AND ((jsta)::text <> ''::text))
25. 0.000 0.020 ↓ 0.0 0 10

Nested Loop (cost=0.72..16.77 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=10)

26. 0.020 0.020 ↑ 1.0 1 10

Index Scan using pk_pcy_policy on pcy_policy p_1 (cost=0.43..8.45 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (id = this_.policy_id)
27. 0.000 0.000 ↓ 0.0 0 10

Index Scan using uq_usr_user_login on usr_user u_1 (cost=0.29..8.31 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=10)

  • Index Cond: ((login)::text = (p_1.owner_user_login)::text)
  • Filter: ((jsta IS NOT NULL) AND ((jsta)::text <> ''::text))
Planning time : 270.017 ms
Execution time : 13,266.725 ms