explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hbBO : original query dbrt 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 73.729 ↓ 0.0 0 1

Nested Loop (cost=3.73..33,276.12 rows=1 width=191) (actual time=73.729..73.729 rows=0 loops=1)

  • Buffers: shared hit=102156
2. 0.000 73.728 ↓ 0.0 0 1

Nested Loop (cost=3.16..33,266.65 rows=1 width=159) (actual time=73.728..73.728 rows=0 loops=1)

  • Buffers: shared hit=102156
3. 1.779 73.728 ↓ 0.0 0 1

Nested Loop (cost=2.73..33,258.53 rows=1 width=151) (actual time=73.728..73.728 rows=0 loops=1)

  • Buffers: shared hit=102156
4. 0.002 0.043 ↓ 2.0 2 1

Nested Loop (cost=0.86..24.64 rows=1 width=16) (actual time=0.038..0.043 rows=2 loops=1)

  • Buffers: shared hit=10
5. 0.002 0.025 ↑ 1.0 1 1

Nested Loop (cost=0.43..9.51 rows=1 width=8) (actual time=0.023..0.025 rows=1 loops=1)

  • Buffers: shared hit=5
6. 0.016 0.016 ↑ 1.0 1 1

Index Scan using "idx$$_192030001" on en_site s (cost=0.43..8.45 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=1)

  • Index Cond: (s.site_id = 457344)
  • Buffers: shared hit=4
7. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on en_site_type site_type (cost=0..1.04 rows=2 width=8) (actual time=0.006..0.007 rows=2 loops=1)

  • Filter: ((site_type.code)::text <> 'RED'::text)
  • Buffers: shared hit=1
8. 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..15.08 rows=4 width=16) (actual time=0.013..0.016 rows=2 loops=1)

  • Index Cond: (sgs.site_id = 457344)
  • Buffers: shared hit=5
9. 22.236 71.906 ↓ 1,223.4 9,787 2

Nested Loop (cost=1.86..33,233.77 rows=8 width=159) (actual time=0.09..35.953 rows=9,787 loops=2)

  • Buffers: shared hit=102146
10. 4.326 30.096 ↓ 1,223.4 9,787 2

Nested Loop (cost=1.44..33,230.23 rows=8 width=151) (actual time=0.08..15.048 rows=9,787 loops=2)

  • Buffers: shared hit=23762
11. 1.554 15.580 ↓ 1,019.0 1,019 2

Nested Loop (cost=1..33,199.43 rows=1 width=146) (actual time=0.055..7.79 rows=1,019 loops=2)

  • Buffers: shared hit=14890
12. 2.852 14.026 ↓ 1,060.0 1,060 2

Nested Loop (cost=1..33,198.38 rows=1 width=154) (actual time=0.053..7.013 rows=1,060 loops=2)

  • Buffers: shared hit=12770
13. 1.066 9.054 ↓ 353.3 1,060 2

Nested Loop (cost=0.72..33,197.36 rows=3 width=154) (actual time=0.045..4.527 rows=1,060 loops=2)

  • Buffers: shared hit=6410
14. 0.402 3.748 ↓ 1.2 1,060 2

Nested Loop (cost=0.43..32,909.8 rows=875 width=97) (actual time=0.033..1.874 rows=1,060 loops=2)

  • Buffers: shared hit=36
15. 0.004 0.004 ↑ 1.0 1 2

Seq Scan on en_offer_status off_status (cost=0..1.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=2)

  • Filter: ((off_status.code)::text = 'ACT'::text)
  • Buffers: shared hit=2
16. 0.418 3.342 ↑ 2.0 1,286 2

Nested Loop (cost=0.43..32,875.96 rows=2,624 width=105) (actual time=0.031..1.671 rows=1,286 loops=2)

  • Buffers: shared hit=34
17. 0.010 0.010 ↑ 1.0 1 2

Seq Scan on en_offer_type off_type (cost=0..1.24 rows=1 width=8) (actual time=0.003..0.005 rows=1 loops=2)

  • Filter: ((off_type.code)::text = 'NBKT'::text)
  • Buffers: shared hit=2
18. 2.914 2.914 ↑ 3.2 1,286 2

Index Scan using en_off_offer_type_id_idx on en_offer off (cost=0.43..32,833.18 rows=4,154 width=105) (actual time=0.026..1.457 rows=1,286 loops=2)

  • Index Cond: (off.offer_type_id = off_type.offer_type_id)
  • Filter: (('2020-01-27 17:32:15.312'::timestamp without time zone >= off.offer_start_dt) AND ('2020-01-27 17:32:15.312'::timestamp without time zone <= off.offer_end_dt))
  • Buffers: shared hit=32
19. 4.240 4.240 ↑ 1.0 1 2,120

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

  • Index Cond: (bas_off.offer_id = off.offer_id)
  • Filter: (bas_off.is_bonus = 'N'::bpchar)
  • Buffers: shared hit=6374
20. 2.120 2.120 ↑ 1.0 1 2,120

Index Scan using en_promotion_pk on en_promotion ep (cost=0.29..0.34 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=2,120)

  • 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))
  • Buffers: shared hit=6360
21. 0.000 0.000 ↑ 1.0 1 2,120

Seq Scan on en_promotion_status eps (cost=0..1.04 rows=1 width=8) (actual time=0..0 rows=1 loops=2,120)

  • Filter: ((eps.code)::text = 'ACT'::text)
  • Buffers: shared hit=2120
22. 10.190 10.190 ↑ 48.7 10 2,038

Index Scan using en_off_site_offer_id_idx on en_offer_site off_site (cost=0.44..25.92 rows=487 width=21) (actual time=0.003..0.005 rows=10 loops=2,038)

  • Index Cond: (off_site.offer_id = off.offer_id)
  • Buffers: shared hit=8872
23. 19.574 19.574 ↑ 1.0 1 19,574

Index Scan using en_promo_part_site_pk on en_promotion_participant_site pr_re_site (cost=0.42..0.44 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=19,574)

  • Index Cond: (pr_re_site.promo_part_site_id = off_site.promo_part_site_id)
  • Buffers: shared hit=78384
24. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: (rw.reward_id = bas_off.reward_id)
25. 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..9.41 rows=2 width=36) (never executed)

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