explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3h5B

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.083 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.13..40.40 rows=1 width=191) (actual time=0.083..0.083 rows=0 loops=1)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, rw.reward_value, rw.reward_type, rw.reward_id, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, off_site.multiplier, off.offer_type_id, pr_re_site.site_group_id, ((((((((((acc_bas_off.account_basket_offer_id)::text || '_'::text) || (acc_bas_off.purchase_balance)::text) || '_'::text) || (acc_bas_off.bal_lock)::text) || '_'::text) || (acc_bas_off.accumulated_balance)::text) || '_'::text) || to_char(acc_bas_off.last_transaction_dt, 'YYYY/MM/DD HH24:MI:SS.FF3'::text)) || '_'::text)
  • Inner Unique: true
  • Join Filter: (acc_bas_off.basket_offer_id = bas_off.basket_offer_id)
2. 0.001 0.083 ↓ 0.0 0 1

Nested Loop (cost=3.56..37.30 rows=1 width=159) (actual time=0.083..0.083 rows=0 loops=1)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off_site.multiplier, pr_re_site.site_group_id, rw.reward_value, rw.reward_type, rw.reward_id
  • Inner Unique: true
3. 0.002 0.082 ↓ 0.0 0 1

Nested Loop (cost=3.12..34.85 rows=1 width=151) (actual time=0.082..0.082 rows=0 loops=1)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off_site.multiplier, pr_re_site.site_group_id
  • Join Filter: ((sgs.site_group_id = pr_re_site.site_group_id) OR (s.site_id = pr_re_site.site_id))
4. 0.001 0.048 ↓ 2.0 2 1

Nested Loop Left Join (cost=0.86..7.71 rows=1 width=16) (actual time=0.046..0.048 rows=2 loops=1)

  • Output: s.site_id, sgs.site_group_id
  • Join Filter: (s.site_id = sgs.site_id)
5. 0.003 0.037 ↑ 1.0 1 1

Nested Loop (cost=0.43..3.51 rows=1 width=8) (actual time=0.036..0.037 rows=1 loops=1)

  • Output: s.site_id
  • Inner Unique: true
  • Join Filter: (s.site_type_id = site_type.site_type_id)
  • Rows Removed by Join Filter: 1
6. 0.030 0.030 ↑ 1.0 1 1

Index Scan using "idx$$_192030001" on endbown.en_site s (cost=0.43..2.45 rows=1 width=16) (actual time=0.030..0.030 rows=1 loops=1)

  • Output: s.site_id, s.user_defined_key, s.name, s.participant_id, s.phone, s.site_type_id, s.site_status_id, s.region, s.ip, s.url, s.create_dt, s.create_user_id, s.last_update_dt, s.last_update_user_id, s.timezone, s.pos_config_id, s.disc_accept_code, s.disc_apply_code, s.unassigned_fuel_code, s.creation_id, s.last_tran_request_id, s.last_tran_request_dt, s.last_tran_request_tz, s.last_tran_id, s.last_tran_token_id, s.last_tran_post_dt, s.geo_latitude, s.geo_longitude, s.geo_update_dt, s.repl_subscription, s.site_custom_msg, s.display_name
  • Index Cond: (s.site_id = 457,344)
7. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on endbown.en_site_type site_type (cost=0.00..1.04 rows=2 width=8) (actual time=0.004..0.004 rows=2 loops=1)

  • Output: site_type.site_type_id, site_type.code, site_type.description
  • Filter: ((site_type.code)::text <> 'RED'::text)
  • Rows Removed by Filter: 1
8. 0.010 0.010 ↑ 2.0 2 1

Index Scan using en_site_gr_siteid_idx on endbown.en_site_group_site sgs (cost=0.43..4.15 rows=4 width=16) (actual time=0.008..0.010 rows=2 loops=1)

  • Output: sgs.site_group_id, sgs.site_id, sgs.site_group_site_create_dt
  • Index Cond: (sgs.site_id = 457,344)
9. 0.000 0.032 ↓ 0.0 0 2

Nested Loop (cost=2.26..27.02 rows=8 width=159) (actual time=0.016..0.016 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off_site.multiplier, pr_re_site.site_group_id, pr_re_site.site_id
  • Inner Unique: true
10. 0.000 0.032 ↓ 0.0 0 2

Nested Loop (cost=1.84..23.48 rows=8 width=151) (actual time=0.016..0.016 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off_site.multiplier, off_site.promo_part_site_id
11. 0.000 0.032 ↓ 0.0 0 2

Nested Loop (cost=1.40..5.60 rows=1 width=146) (actual time=0.016..0.016 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.offer_id, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id
  • Inner Unique: true
12. 0.000 0.032 ↓ 0.0 0 2

Nested Loop (cost=1.27..5.43 rows=1 width=154) (actual time=0.016..0.016 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.offer_id, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off.offer_status_id
  • Inner Unique: true
13. 0.000 0.032 ↓ 0.0 0 2

Nested Loop (cost=1.13..5.25 rows=1 width=154) (actual time=0.016..0.016 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.offer_id, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off.offer_status_id
  • Inner Unique: true
14. 0.000 0.032 ↓ 0.0 0 2

Nested Loop (cost=1.00..5.08 rows=1 width=162) (actual time=0.016..0.016 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.offer_id, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off.offer_status_id, ep.promotion_status_id
  • Inner Unique: true
15. 0.002 0.032 ↓ 0.0 0 2

Nested Loop (cost=0.72..4.76 rows=1 width=162) (actual time=0.016..0.016 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.basket_offer_type_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.offer_id, bas_off.reward_id, off.offer_desc, off.offer_id, off.user_defined_key, off.offer_start_dt, off.offer_end_dt, off.offer_type_id, off.promotion_id, off.offer_status_id
  • Inner Unique: true
16. 0.030 0.030 ↓ 0.0 0 2

Index Scan using en_basket_offer_is_bonus_idx on endbown.en_basket_offer bas_off (cost=0.29..2.30 rows=1 width=57) (actual time=0.015..0.015 rows=0 loops=2)

  • Output: bas_off.basket_offer_id, bas_off.offer_id, bas_off.basket_size, bas_off.min_basket, bas_off.max_basket, bas_off.acc_max_basket, bas_off.code, bas_off.reward_id, bas_off.create_dt, bas_off.create_user_id, bas_off.last_update_dt, bas_off.last_update_user_id, bas_off.effective_date_type_id, bas_off.no_of_days, bas_off.max_issuance_count, bas_off.is_bonus, bas_off.basket_offer_type_id
  • Index Cond: (bas_off.is_bonus = '$5'::bpchar)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using en_offer_pk on endbown.en_offer off (cost=0.43..2.46 rows=1 width=105) (never executed)

  • Output: off.offer_id, off.user_defined_key, off.offer_type_id, off.offer_desc, off.offer_start_dt, off.offer_end_dt, off.sponsor, off.offer_status_id, off.participant_offer_id, off.participant_id, off.site_id, off.promotion_id, off.max_reward, off.max_reward_unit, off.sponsor_id, off.create_dt, off.create_user_id, off.last_update_dt, off.last_update_user_id, off.parent_offer_id, off.offer_eligibility, off.display_name, off.reg_required, off.num_days, off.total_issuance_count, off.issuance_count
  • Index Cond: (off.offer_id = bas_off.offer_id)
  • Filter: ((CURRENT_TIMESTAMP >= off.offer_start_dt) AND (CURRENT_TIMESTAMP <= off.offer_end_dt))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using en_promotion_pk on endbown.en_promotion ep (cost=0.29..0.32 rows=1 width=16) (never executed)

  • Output: ep.promotion_id, ep.user_defined_key, ep.promotion_name, ep.promotion_status_id, ep.promotion_start_dt, ep.promotion_end_dt, ep.participant_id, ep.batch_file_id, ep.create_dt, ep.create_user_id, ep.last_update_dt, ep.last_update_user_id, ep.promo_type, ep.total_issuance_count
  • Index Cond: (ep.promotion_id = off.promotion_id)
  • Filter: ((CURRENT_TIMESTAMP >= ep.promotion_start_dt) AND (CURRENT_TIMESTAMP <= ep.promotion_end_dt))
19. 0.000 0.000 ↓ 0.0 0

Index Scan using en_promotion_status_pk on endbown.en_promotion_status eps (cost=0.13..0.15 rows=1 width=8) (never executed)

  • Output: eps.promotion_status_id, eps.code, eps.description
  • Index Cond: (eps.promotion_status_id = ep.promotion_status_id)
  • Filter: ((eps.code)::text = 'ACT'::text)
20. 0.000 0.000 ↓ 0.0 0

Index Only Scan using en_off_code_comp_idx on endbown.en_offer_type off_type (cost=0.14..0.16 rows=1 width=8) (never executed)

  • Output: off_type.code, off_type.offer_type_id
  • Index Cond: ((off_type.code = 'NBKT'::text) AND (off_type.offer_type_id = off.offer_type_id))
  • Heap Fetches: 0
21. 0.000 0.000 ↓ 0.0 0

Index Scan using en_offer_status_pk on endbown.en_offer_status off_status (cost=0.13..0.15 rows=1 width=8) (never executed)

  • Output: off_status.offer_status_id, off_status.code, off_status.description
  • Index Cond: (off_status.offer_status_id = off.offer_status_id)
  • Filter: ((off_status.code)::text = 'ACT'::text)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using en_off_site_offer_id_idx on endbown.en_offer_site off_site (cost=0.44..13.01 rows=487 width=21) (never executed)

  • Output: off_site.offer_site_id, off_site.offer_id, off_site.promo_part_site_id, off_site.create_dt, off_site.multiplier
  • Index Cond: (off_site.offer_id = off.offer_id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using en_promo_part_site_pk on endbown.en_promotion_participant_site pr_re_site (cost=0.42..0.44 rows=1 width=24) (never executed)

  • Output: pr_re_site.promo_part_site_id, pr_re_site.promo_participant_id, pr_re_site.site_group_id, pr_re_site.site_id, pr_re_site.create_dt
  • Index Cond: (pr_re_site.promo_part_site_id = off_site.promo_part_site_id)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using en_reward_pk on endbown.en_reward rw (cost=0.44..2.46 rows=1 width=16) (never executed)

  • Output: rw.reward_id, rw.offer_id, rw.offer_type_id, rw.reward_status_id, rw.reward_value, rw.participant_reward_id, rw.reward_type, rw.sponsor_id, rw.create_dt, rw.create_user_id, rw.last_update_dt, rw.last_update_user_id, rw.reward_unit
  • Index Cond: (rw.reward_id = bas_off.reward_id)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using en_acc_bskt_off_account_id_idx on endbown.en_account_basket_offer acc_bas_off (cost=0.57..3.02 rows=3 width=36) (never executed)

  • Output: acc_bas_off.account_basket_offer_id, acc_bas_off.account_id, acc_bas_off.basket_offer_id, acc_bas_off.purchase_balance, acc_bas_off.bal_lock, acc_bas_off.bal_lock_dt, acc_bas_off.last_transaction_dt, acc_bas_off.active, acc_bas_off.accumulated_balance, acc_bas_off.transaction_id, acc_bas_off.purchase_by_date
  • Index Cond: (acc_bas_off.account_id = 630,631,466)
  • Filter: ((acc_bas_off.active)::text = 'Y'::text)
Planning time : 8.715 ms
Execution time : 0.319 ms