explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qsFd

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 954.293 ↓ 10.0 10 1

Limit (cost=15,502.57..15,561.58 rows=1 width=296) (actual time=953.977..954.293 rows=10 loops=1)

2. 0.041 954.289 ↓ 10.0 10 1

Result (cost=15,502.57..15,561.58 rows=1 width=296) (actual time=953.976..954.289 rows=10 loops=1)

3. 0.106 953.878 ↓ 10.0 10 1

Sort (cost=15,502.57..15,502.57 rows=1 width=232) (actual time=953.875..953.878 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.277 953.772 ↓ 18.0 18 1

Nested Loop (cost=534.98..15,502.56 rows=1 width=232) (actual time=192.657..953.772 rows=18 loops=1)

5. 0.047 953.279 ↓ 18.0 18 1

Nested Loop Left Join (cost=534.55..15,502.02 rows=1 width=192) (actual time=192.631..953.279 rows=18 loops=1)

6. 0.034 953.034 ↓ 18.0 18 1

Nested Loop (cost=534.12..15,494.32 rows=1 width=191) (actual time=192.613..953.034 rows=18 loops=1)

7. 0.085 952.892 ↓ 18.0 18 1

Nested Loop (cost=533.84..15,494.01 rows=1 width=207) (actual time=192.600..952.892 rows=18 loops=1)

8. 0.065 952.503 ↑ 3.1 38 1

Nested Loop (cost=533.55..15,456.13 rows=117 width=199) (actual time=192.563..952.503 rows=38 loops=1)

9. 0.293 861.142 ↑ 2.7 18 1

Nested Loop (cost=533.11..14,826.72 rows=49 width=175) (actual time=187.420..861.142 rows=18 loops=1)

10. 0.249 0.249 ↓ 2.4 50 1

Seq Scan on prd_product prd4_ (cost=0.00..13.29 rows=21 width=34) (actual time=0.011..0.249 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
11. 7.100 860.600 ↓ 0.0 0 50

Bitmap Heap Scan on pcy_policy pcy2_ (cost=533.11..705.39 rows=1 width=185) (actual time=17.157..17.212 rows=0 loops=50)

  • Recheck Cond: ((((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)) AND (product_id = prd4_.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 (((prd4_.product_class = 'CP'::text) AND ((user_login)::text = '082949'::text)) OR ((prd4_.product_class = 'CC'::text) AND (((unit)::text = '052935'::text) OR ((user_login)::text = '082949'::text))) OR ((prd4_.product_class = 'CP'::text) AND ((owner_user_login)::text = '082949'::text)) OR ((prd4_.product_class = 'CC'::text) AND (((owner_unit)::text = '052935'::text) OR ((owner_user_login)::text = '082949'::text)))) AND CASE WHEN (mandatory_policy IS TRUE) THEN ((('now'::cstring)::date < ((dko_oc)::date + '1 day'::interval)) AND ((((LEAST(protection_start_date, dpo_oc))::date + '1 year'::interval) - (((prd4_.days_to_policy_renewal)::text || ' days'::text))::interval) <= ('now'::cstring)::date)) ELSE CASE WHEN ((mandatory_policy IS FALSE) OR (mandatory_policy IS NULL)) THEN ((('now'::cstring)::date >= (((protection_start_date)::date + '1 year'::interval) - (((prd4_.days_to_policy_renewal)::text || ' days'::text))::interval)) AND ((((protection_start_date)::date + '1 year'::interval) + '30 days'::interval) >= ('now'::cstring)::date)) ELSE false END END)
  • Rows Removed by Filter: 44
  • Heap Blocks: exact=2112
12. 45.200 853.500 ↓ 0.0 0 50

BitmapAnd (cost=533.11..533.11 rows=42 width=0) (actual time=17.070..17.070 rows=0 loops=50)

13. 0.150 133.200 ↓ 0.0 0 50

BitmapOr (cost=168.03..168.03 rows=7,526 width=0) (actual time=2.664..2.664 rows=0 loops=50)

14. 23.350 23.350 ↓ 2.6 198 50

Bitmap Index Scan on idx_policy_user_login (cost=0.00..4.99 rows=75 width=0) (actual time=0.467..0.467 rows=198 loops=50)

  • Index Cond: ((user_login)::text = '082949'::text)
15. 106.700 106.700 ↓ 1.0 7,577 50

Bitmap Index Scan on idx_policy_unit (cost=0.00..139.48 rows=7,340 width=0) (actual time=2.134..2.134 rows=7,577 loops=50)

  • Index Cond: ((unit)::text = '052935'::text)
16. 2.000 2.000 ↓ 2.6 198 50

Bitmap Index Scan on idx_policy_user_login (cost=0.00..4.99 rows=75 width=0) (actual time=0.040..0.040 rows=198 loops=50)

  • Index Cond: ((user_login)::text = '082949'::text)
17. 0.350 0.350 ↓ 0.0 0 50

Bitmap Index Scan on idx_pcy_policy_owner_login (cost=0.00..4.52 rows=12 width=0) (actual time=0.007..0.007 rows=0 loops=50)

  • Index Cond: ((owner_user_login)::text = '082949'::text)
18. 0.400 0.400 ↑ 5.5 2 50

Bitmap Index Scan on idx_pcy_policy_owner_unit (cost=0.00..4.51 rows=11 width=0) (actual time=0.008..0.008 rows=2 loops=50)

  • Index Cond: ((owner_unit)::text = '052935'::text)
19. 0.250 0.250 ↓ 0.0 0 50

Bitmap Index Scan on idx_pcy_policy_owner_login (cost=0.00..4.52 rows=12 width=0) (actual time=0.005..0.005 rows=0 loops=50)

  • Index Cond: ((owner_user_login)::text = '082949'::text)
20. 675.100 675.100 ↓ 1.5 31,419 50

Bitmap Index Scan on idx_policy_product_id (cost=0.00..364.83 rows=20,345 width=0) (actual time=13.502..13.502 rows=31,419 loops=50)

  • Index Cond: (product_id = prd4_.id)
21. 91.296 91.296 ↑ 1.5 2 18

Index Scan using idx_policy_id on pcy_person_value this_ (cost=0.43..12.82 rows=3 width=24) (actual time=5.070..5.072 rows=2 loops=18)

  • Index Cond: (policy_id = pcy2_.id)
22. 0.304 0.304 ↓ 0.0 0 38

Index Scan using pk_prd_item on prd_item g3_1_ (cost=0.29..0.31 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=38)

  • Index Cond: (id = this_.group_id)
  • Filter: ((alias)::text = 'UBEZPIECZAJACY'::text)
  • Rows Removed by Filter: 1
23. 0.108 0.108 ↑ 1.0 1 18

Index Only Scan using pk_prd_group on prd_group g3_ (cost=0.29..0.30 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=18)

  • Index Cond: (id = this_.group_id)
  • Heap Fetches: 18
24. 0.198 0.198 ↑ 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.011..0.011 rows=1 loops=18)

  • Index Cond: (pcy2_.id = id)
25. 0.216 0.216 ↑ 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.011..0.012 rows=1 loops=18)

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

SubPlan (for Result)

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

  • Index Cond: (id = prs1_.address_corr)
30. 0.030 0.200 ↑ 1.0 1 10

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

31. 0.080 0.080 ↑ 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.007..0.008 rows=1 loops=10)

  • Index Cond: (id = this_.policy_id)
32. 0.090 0.090 ↑ 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.009..0.009 rows=1 loops=10)

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

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

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

  • Index Cond: (id = this_.policy_id)
35. 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 : 12.641 ms
Execution time : 954.662 ms