explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PYxp

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 12,278.882 ↓ 10.0 10 1

Limit (cost=22,799.11..22,858.13 rows=1 width=297) (actual time=12,111.518..12,278.882 rows=10 loops=1)

2. 0.132 12,278.875 ↓ 10.0 10 1

Result (cost=22,799.11..22,858.13 rows=1 width=297) (actual time=12,111.516..12,278.875 rows=10 loops=1)

3. 0.289 12,071.743 ↓ 10.0 10 1

Sort (cost=22,799.11..22,799.11 rows=1 width=233) (actual time=12,071.732..12,071.743 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: top-N heapsort Memory: 27kB
4. 0.999 12,071.454 ↓ 37.0 37 1

Nested Loop (cost=2.42..22,799.10 rows=1 width=233) (actual time=4,280.777..12,071.454 rows=37 loops=1)

5. 0.148 11,083.110 ↓ 37.0 37 1

Nested Loop Left Join (cost=2.00..22,798.57 rows=1 width=193) (actual time=4,212.788..11,083.110 rows=37 loops=1)

6. 6.997 10,534.400 ↓ 37.0 37 1

Nested Loop (cost=1.57..22,790.97 rows=1 width=192) (actual time=4,162.834..10,534.400 rows=37 loops=1)

  • Join Filter: ((pcy2_.product_id = prd4_.id) AND (((prd4_.product_class = 'CP'::text) AND (((pcy2_.unit)::text = '007095'::text) OR ((pcy2_.user_login)::text = '064278'::text))) OR ((prd4_.product_class = 'CC'::text) AND (((pcy2_.unit)::text = '007095'::text) OR ((pcy2_.user_login)::text = '064278'::text))) OR ((prd4_.product_class = '007095'::text) AND (((pcy2_.owner_unit)::text = '007095'::text) OR ((pcy2_.owner_user_login)::text = '064278'::text))) OR ((prd4_.product_class = 'CC'::text) AND (((pcy2_.owner_unit)::text = '007095'::text) OR ((pcy2_.owner_user_login)::text = '064278'::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: 49683
7. 1,718.357 10,525.143 ↓ 56.5 1,130 1

Nested Loop (cost=1.57..22,732.51 rows=20 width=202) (actual time=242.553..10,525.143 rows=1,130 loops=1)

8. 397.499 1,842.246 ↓ 182.9 3,482,270 1

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

9. 0.126 1.327 ↓ 9.0 180 1

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

10. 0.301 0.301 ↑ 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.019..0.301 rows=180 loops=1)

  • Index Cond: ((alias)::text = 'UBEZPIECZAJACY'::text)
11. 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
12. 1,443.420 1,443.420 ↓ 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.019 rows=19,346 loops=180)

  • Index Cond: (group_id = g3_.id)
13. 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 = '007095'::text) OR ((user_login)::text = '064278'::text) OR ((unit)::text = '007095'::text) OR ((user_login)::text = '064278'::text) OR ((owner_unit)::text = '007095'::text) OR ((owner_user_login)::text = '064278'::text) OR ((owner_unit)::text = '007095'::text) OR ((owner_user_login)::text = '064278'::text)))
  • Rows Removed by Filter: 1
14. 2.177 2.260 ↓ 2.8 44 1,130

Materialize (cost=0.00..14.50 rows=16 width=34) (actual time=0.000..0.002 rows=44 loops=1,130)

15. 0.083 0.083 ↓ 2.8 44 1

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

  • Filter: ((days_to_policy_renewal <> '-1'::integer) AND ((product_class = 'CP'::text) OR (product_class = 'CC'::text) OR (product_class = '007095'::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
16. 548.562 548.562 ↑ 1.0 1 37

Index Scan using pk_pcy_policy_additional on pcy_policy_additional pcy2_1_ (cost=0.43..7.59 rows=1 width=9) (actual time=14.825..14.826 rows=1 loops=37)

  • Index Cond: (pcy2_.id = id)
17. 987.345 987.345 ↑ 1.0 1 37

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

  • 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. 109.030 109.030 ↑ 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=10.902..10.903 rows=1 loops=10)

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

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

23. 0.120 0.120 ↑ 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.012..0.012 rows=1 loops=10)

  • Index Cond: (id = this_.policy_id)
24. 97.650 97.650 ↑ 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=9.764..9.765 rows=1 loops=10)

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

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

26. 0.080 0.080 ↑ 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.008..0.008 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.382 ms
Execution time : 12,279.615 ms