explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PZfy

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 7,427.366 ↓ 0.0 0 1

Limit (cost=19,478.54..19,537.55 rows=1 width=299) (actual time=7,427.366..7,427.366 rows=0 loops=1)

2. 0.000 7,427.364 ↓ 0.0 0 1

Result (cost=19,478.54..19,537.55 rows=1 width=299) (actual time=7,427.364..7,427.364 rows=0 loops=1)

3. 0.022 7,427.364 ↓ 0.0 0 1

Sort (cost=19,478.54..19,478.54 rows=1 width=235) (actual time=7,427.364..7,427.364 rows=0 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: 25kB
4. 0.000 7,427.342 ↓ 0.0 0 1

Nested Loop (cost=473.13..19,478.53 rows=1 width=235) (actual time=7,427.342..7,427.342 rows=0 loops=1)

5. 0.001 7,427.342 ↓ 0.0 0 1

Nested Loop Left Join (cost=472.70..19,478.01 rows=1 width=195) (actual time=7,427.342..7,427.342 rows=0 loops=1)

6. 1.047 7,427.341 ↓ 0.0 0 1

Nested Loop (cost=472.27..19,470.23 rows=1 width=194) (actual time=7,427.341..7,427.341 rows=0 loops=1)

7. 1,181.040 7,421.148 ↓ 257.3 2,573 1

Nested Loop (cost=472.00..19,465.40 rows=10 width=203) (actual time=500.549..7,421.148 rows=2,573 loops=1)

8. 287.484 1,267.736 ↓ 201.6 2,486,186 1

Nested Loop (cost=471.57..12,565.79 rows=12,335 width=16) (actual time=3.972..1,267.736 rows=2,486,186 loops=1)

9. 0.474 5.972 ↓ 8.9 276 1

Hash Join (cost=471.14..1,347.05 rows=31 width=16) (actual time=3.848..5.972 rows=276 loops=1)

  • Hash Cond: (g3_1_.id = g3_.id)
10. 1.766 1.827 ↓ 1.0 276 1

Bitmap Heap Scan on prd_item g3_1_ (cost=14.52..889.11 rows=271 width=8) (actual time=0.097..1.827 rows=276 loops=1)

  • Recheck Cond: ((alias)::text = 'UBEZPIECZAJACY'::text)
  • Heap Blocks: exact=290
11. 0.061 0.061 ↓ 1.1 296 1

Bitmap Index Scan on idx_product_item_alias (cost=0.00..14.45 rows=271 width=0) (actual time=0.061..0.061 rows=296 loops=1)

  • Index Cond: ((alias)::text = 'UBEZPIECZAJACY'::text)
12. 1.720 3.671 ↓ 1.0 14,495 1

Hash (cost=275.72..275.72 rows=14,472 width=8) (actual time=3.671..3.671 rows=14,495 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 695kB
13. 1.951 1.951 ↓ 1.0 14,495 1

Seq Scan on prd_group g3_ (cost=0.00..275.72 rows=14,472 width=8) (actual time=0.005..1.951 rows=14,495 loops=1)

14. 974.280 974.280 ↑ 1.4 9,008 276

Index Scan using idx_group_id on pcy_person_value this_ (cost=0.43..239.74 rows=12,215 width=24) (actual time=0.007..3.530 rows=9,008 loops=276)

  • Index Cond: (group_id = g3_.id)
15. 4,972.372 4,972.372 ↓ 0.0 0 2,486,186

Index Scan using pk_pcy_policy on pcy_policy pcy2_ (cost=0.43..0.55 rows=1 width=187) (actual time=0.002..0.002 rows=0 loops=2,486,186)

  • 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. 5.146 5.146 ↓ 0.0 0 2,573

Index Scan using pk_prd_product on prd_product prd4_ (cost=0.27..0.47 rows=1 width=34) (actual time=0.002..0.002 rows=0 loops=2,573)

  • Index Cond: (id = pcy2_.product_id)
  • Filter: ((days_to_policy_renewal <> '-1'::integer) AND (((product_class = 'CP'::text) AND ((pcy2_.user_login)::text = '082949'::text)) OR ((product_class = 'CC'::text) AND (((pcy2_.unit)::text = '052935'::text) OR ((pcy2_.user_login)::text = '082949'::text))) OR ((product_class = 'CP'::text) AND ((pcy2_.owner_user_login)::text = '082949'::text)) OR ((product_class = 'CC'::text) AND (((pcy2_.owner_unit)::text = '052935'::text) OR ((pcy2_.owner_user_login)::text = '082949'::text)))) 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[])) 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) - (((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) - (((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 Filter: 1
17. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_pcy_policy_additional on pcy_policy_additional pcy2_1_ (cost=0.43..7.76 rows=1 width=9) (never executed)

  • Index Cond: (pcy2_.id = id)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_prs_person on prs_person prs1_ (cost=0.43..0.47 rows=1 width=70) (never executed)

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

SubPlan (for Result)

20. 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)
21. 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)
22. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = prs1_.address_corr)
23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..16.77 rows=1 width=32) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_pcy_policy on pcy_policy p (cost=0.43..8.45 rows=1 width=7) (never executed)

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

Index Scan using uq_usr_user_login on usr_user u (cost=0.29..8.31 rows=1 width=23) (never executed)

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

Nested Loop (cost=0.72..16.77 rows=1 width=32) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_pcy_policy on pcy_policy p_1 (cost=0.43..8.45 rows=1 width=7) (never executed)

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

Index Scan using uq_usr_user_login on usr_user u_1 (cost=0.29..8.31 rows=1 width=23) (never executed)

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