explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YJ7b

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

Nested Loop (cost=133.08..307.8 rows=1 width=191) (actual time=19.697..19.697 rows=0 loops=1)

  • Buffers: shared hit=8719
2. 0.000 19.697 ↓ 0.0 0 1

Nested Loop (cost=132.51..305.16 rows=1 width=159) (actual time=19.697..19.697 rows=0 loops=1)

  • Buffers: shared hit=8719
3. 0.001 19.697 ↓ 0.0 0 1

Nested Loop (cost=132.08..302.79 rows=1 width=151) (actual time=19.697..19.697 rows=0 loops=1)

  • Buffers: shared hit=8719
4. 0.000 19.696 ↓ 0.0 0 1

Nested Loop (cost=131.79..300.73 rows=1 width=159) (actual time=19.696..19.696 rows=0 loops=1)

  • Buffers: shared hit=8719
5. 0.000 19.696 ↓ 0.0 0 1

Nested Loop (cost=131.36..298.28 rows=1 width=167) (actual time=19.696..19.696 rows=0 loops=1)

  • Buffers: shared hit=8719
6. 2.986 12.007 ↓ 9,880.0 9,880 1

Nested Loop (cost=1.4..166.83 rows=1 width=159) (actual time=0.071..12.007 rows=9,880 loops=1)

  • Buffers: shared hit=8703
7. 1.042 4.781 ↓ 1,060.0 1,060 1

Nested Loop (cost=0.84..149.87 rows=1 width=154) (actual time=0.047..4.781 rows=1,060 loops=1)

  • Buffers: shared hit=3277
8. 1.619 1.619 ↓ 30.3 1,060 1

Index Only Scan using en_offer_offer_start_dt_idx_dlm on en_offer off (cost=0.56..84.85 rows=35 width=97) (actual time=0.032..1.619 rows=1,060 loops=1)

  • Index Cond: ((off.offer_type_id = 3) AND (off.offer_start_dt <= '2020-01-27 17:32:15.312'::timestamp without time zone) AND (off.offer_end_dt >= '2020-01-27 17:32:15.312'::timestamp without time zone))
  • Filter: (off.offer_status_id = 2)
  • Heap Fetches: 1286
  • Buffers: shared hit=90
9. 2.120 2.120 ↑ 1.0 1 1,060

Index Scan using en_basket_offer_offer_id on en_basket_offer bas_off (cost=0.29..1.85 rows=1 width=57) (actual time=0.002..0.002 rows=1 loops=1,060)

  • Index Cond: (bas_off.offer_id = off.offer_id)
  • Filter: (bas_off.is_bonus = 'N'::bpchar)
  • Buffers: shared hit=3187
10. 4.240 4.240 ↑ 54.1 9 1,060

Index Only Scan using en_offer_site_promo_part_site_id_idx_dlm on en_offer_site off_site (cost=0.56..12.09 rows=487 width=21) (actual time=0.003..0.004 rows=9 loops=1,060)

  • Index Cond: (off_site.offer_id = off.offer_id)
  • Buffers: shared hit=5426
11. 9.831 9.880 ↑ 23.0 2 9,880

HashAggregate (cost=129.96..130.42 rows=46 width=24) (actual time=0..0.001 rows=2 loops=9,880)

  • Group Key: ps1.promo_part_site_id, ps1.site_id, ps1.site_group_id
  • Buffers: shared hit=16
12. 0.002 0.049 ↑ 23.0 2 1

Append (cost=0.42..129.61 rows=46 width=24) (actual time=0.018..0.049 rows=2 loops=1)

  • Buffers: shared hit=16
13. 0.019 0.019 ↑ 14.0 2 1

Index Scan using en_promotion_partition_site_site_idx on en_promotion_participant_site ps1 (cost=0.42..28.65 rows=28 width=24) (actual time=0.017..0.019 rows=2 loops=1)

  • Index Cond: (ps1.site_id = 457344)
  • Buffers: shared hit=5
14. 0.002 0.028 ↓ 0.0 0 1

Nested Loop (cost=0.86..100.27 rows=18 width=24) (actual time=0.028..0.028 rows=0 loops=1)

  • Buffers: shared hit=11
15. 0.016 0.016 ↑ 2.0 2 1

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

  • Index Cond: (sgs.site_id = 457344)
  • Buffers: shared hit=5
16. 0.010 0.010 ↓ 0.0 0 2

Index Scan using en_promotion_participant_site_site_group_id_idx on en_promotion_participant_site ps (cost=0.42..23.79 rows=24 width=16) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: (ps.site_group_id = sgs.site_group_id)
  • Buffers: shared hit=6
17. 0.000 0.000 ↓ 0.0 0 0

Index Scan using "idx$$_192030001" on en_site s (cost=0.43..2.45 rows=1 width=8) (never executed)

  • Index Cond: (s.site_id = ps1.site_id)
  • Filter: (s.site_type_id = ANY ('{1,3}'::bigint[]))
18. 0.000 0.000 ↓ 0.0 0 0

Index Scan using en_promotion_pk on en_promotion ep (cost=0.29..2.06 rows=1 width=8) (never executed)

  • Index Cond: (ep.promotion_id = off.promotion_id)
  • Filter: (('2020-01-27 17:32:15.312'::timestamp without time zone >= ep.promotion_start_dt) AND ('2020-01-27 17:32:15.312'::timestamp without time zone <= ep.promotion_end_dt) AND (ep.promotion_status_id = 2))
19. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: (rw.reward_id = bas_off.reward_id)
20. 0.000 0.000 ↓ 0.0 0 0

Index Scan using en_acc_bskt_off_account_id_idx on en_account_basket_offer acc_bas_off (cost=0.57..2.59 rows=1 width=36) (never executed)

  • Index Cond: (acc_bas_off.account_id = 630631466)
  • Filter: ((acc_bas_off.active)::text = 'Y'::text)
Planning time : 4.031 ms
Execution time : 19.869 ms