explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a9EE

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 11,409.663 ↓ 10.0 10 1

Limit (cost=24,034.83..24,093.84 rows=1 width=296) (actual time=11,409.495..11,409.663 rows=10 loops=1)

2. 0.021 11,409.662 ↓ 10.0 10 1

Result (cost=24,034.83..24,093.84 rows=1 width=296) (actual time=11,409.494..11,409.662 rows=10 loops=1)

3. 0.042 11,409.441 ↓ 10.0 10 1

Sort (cost=24,034.83..24,034.83 rows=1 width=232) (actual time=11,409.438..11,409.441 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.055 11,409.399 ↓ 18.0 18 1

Nested Loop (cost=2.42..24,034.82 rows=1 width=232) (actual time=11,361.080..11,409.399 rows=18 loops=1)

5. 0.008 11,409.254 ↓ 18.0 18 1

Nested Loop Left Join (cost=2.00..24,034.29 rows=1 width=192) (actual time=11,361.060..11,409.254 rows=18 loops=1)

6. 42.722 11,409.174 ↓ 18.0 18 1

Nested Loop (cost=1.57..24,026.58 rows=1 width=191) (actual time=11,361.050..11,409.174 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.102 0.102 ↓ 2.4 50 1

Seq Scan on prd_product prd4_ (cost=0.00..13.29 rows=21 width=34) (actual time=0.016..0.102 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.311 11,366.350 ↓ 257.1 4,627 50

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

9. 2,069.930 11,354.039 ↓ 257.1 4,627 1

Nested Loop (cost=1.57..23,963.17 rows=18 width=201) (actual time=569.118..11,354.039 rows=4,627 loops=1)

10. 422.604 1,919.357 ↓ 183.4 3,682,376 1

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

11. 0.306 1.313 ↓ 8.9 186 1

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

12. 0.263 0.263 ↑ 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=0.019..0.263 rows=186 loops=1)

  • Index Cond: ((alias)::text = 'UBEZPIECZAJACY'::text)
13. 0.744 0.744 ↑ 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.004 rows=1 loops=186)

  • Index Cond: (id = g3_1_.id)
  • Heap Fetches: 186
14. 1,495.440 1,495.440 ↓ 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.008..8.040 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.072 0.072 ↑ 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.004..0.004 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.070 0.070 ↑ 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.007 rows=1 loops=10)

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

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

23. 0.060 0.060 ↑ 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.006 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.010 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.010 0.010 ↑ 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.001..0.001 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 : 11.674 ms
Execution time : 11,410.068 ms