explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hxls

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 10,520.217 ↓ 10.0 10 1

Limit (cost=22,781.81..22,840.83 rows=1 width=297) (actual time=10,520.065..10,520.217 rows=10 loops=1)

2. 0.033 10,520.215 ↓ 10.0 10 1

Result (cost=22,781.81..22,840.83 rows=1 width=297) (actual time=10,520.063..10,520.215 rows=10 loops=1)

3. 0.036 10,519.992 ↓ 10.0 10 1

Sort (cost=22,781.81..22,781.82 rows=1 width=233) (actual time=10,519.992..10,519.992 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: 30kB
4. 0.059 10,519.956 ↓ 19.0 19 1

Nested Loop (cost=2.42..22,781.80 rows=1 width=233) (actual time=10,515.012..10,519.956 rows=19 loops=1)

5. 0.005 10,519.802 ↓ 19.0 19 1

Nested Loop Left Join (cost=2.00..22,781.27 rows=1 width=193) (actual time=10,514.978..10,519.802 rows=19 loops=1)

6. 6.667 10,519.702 ↓ 19.0 19 1

Nested Loop (cost=1.57..22,773.36 rows=1 width=192) (actual time=10,514.967..10,519.702 rows=19 loops=1)

  • Join Filter: ((pcy2_.product_id = prd4_.id) AND (((prd4_.product_class = 'CP'::text) AND (((pcy2_.unit)::text = '007207'::text) OR ((pcy2_.user_login)::text = '069232'::text))) OR ((prd4_.product_class = 'CC'::text) AND (((pcy2_.unit)::text = '007207'::text) OR ((pcy2_.user_login)::text = '069232'::text))) OR ((prd4_.product_class = '007207'::text) AND (((pcy2_.owner_unit)::text = '007207'::text) OR ((pcy2_.owner_user_login)::text = '069232'::text))) OR ((prd4_.product_class = 'CC'::text) AND (((pcy2_.owner_unit)::text = '007207'::text) OR ((pcy2_.owner_user_login)::text = '069232'::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: 33025
7. 0.115 0.115 ↓ 2.8 44 1

Seq Scan on prd_product prd4_ (cost=0.00..14.42 rows=16 width=34) (actual time=0.013..0.115 rows=44 loops=1)

  • Filter: ((days_to_policy_renewal <> '-1'::integer) AND ((product_class = 'CP'::text) OR (product_class = 'CC'::text) OR (product_class = '007207'::text) OR (product_class = 'CC'::text)) AND ((code)::text = ANY ('{Agro_GM14,rubiAgroEkspert,AutoAssist,AutoAssistDirect,Biznes_Cesja,A15,ConcordiaBiznes,ConcordiaDOM,CMD,CP,CBIZwierzeta,DSZ-BB1,OCBUP,OCCERT,OCPosrednikow,OP,DSZ,SASS,GRKBIndSam}'::text[])))
  • Rows Removed by Filter: 138
8. 2.000 10,512.920 ↓ 62.6 751 44

Materialize (cost=1.57..22,732.57 rows=12 width=202) (actual time=5.330..238.930 rows=751 loops=44)

9. 1,679.675 10,510.920 ↓ 62.6 751 1

Nested Loop (cost=1.57..22,732.51 rows=12 width=202) (actual time=234.533..10,510.920 rows=751 loops=1)

10. 404.295 1,866.705 ↓ 182.9 3,482,270 1

Nested Loop (cost=1.14..12,216.50 rows=19,041 width=16) (actual time=0.055..1,866.705 rows=3,482,270 loops=1)

11. 0.106 1.350 ↓ 9.0 180 1

Nested Loop (cost=0.70..1,130.26 rows=20 width=16) (actual time=0.040..1.350 rows=180 loops=1)

12. 0.344 0.344 ↑ 1.0 180 1

Index Scan using idx_product_item_alias on prd_item g3_1_ (cost=0.42..702.01 rows=180 width=8) (actual time=0.030..0.344 rows=180 loops=1)

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

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

  • Index Cond: (id = g3_1_.id)
  • Heap Fetches: 180
14. 1,461.060 1,461.060 ↓ 1.0 19,346 180

Index Scan using idx_group_id on pcy_person_value this_ (cost=0.43..369.88 rows=18,443 width=24) (actual time=0.009..8.117 rows=19,346 loops=180)

  • Index Cond: (group_id = g3_.id)
15. 6,964.540 6,964.540 ↓ 0.0 0 3,482,270

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

  • 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 (((unit)::text = '007207'::text) OR ((user_login)::text = '069232'::text) OR ((unit)::text = '007207'::text) OR ((user_login)::text = '069232'::text) OR ((owner_unit)::text = '007207'::text) OR ((owner_user_login)::text = '069232'::text) OR ((owner_unit)::text = '007207'::text) OR ((owner_user_login)::text = '069232'::text)))
  • Rows Removed by Filter: 1
16. 0.095 0.095 ↑ 1.0 1 19

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

  • Index Cond: (pcy2_.id = id)
17. 0.095 0.095 ↑ 1.0 1 19

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

  • 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.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.030 0.030 ↑ 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.003..0.003 rows=1 loops=10)

  • Index Cond: (id = this_.policy_id)
24. 0.050 0.050 ↑ 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.005..0.005 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 : 10.669 ms
Execution time : 10,520.736 ms