explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QEAG : Improved query on DB RT 2

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

Nested Loop (cost=3.98..32,398.47 rows=4 width=192) (actual time=39.434..39.434 rows=0 loops=1)

  • Buffers: shared hit=42,233
2. 0.000 39.433 ↓ 0.0 0 1

Nested Loop (cost=3.41..32,387.88 rows=4 width=160) (actual time=39.433..39.433 rows=0 loops=1)

  • Buffers: shared hit=42,233
3. 6.642 39.433 ↓ 0.0 0 1

Nested Loop (cost=2.97..32,355.43 rows=4 width=152) (actual time=39.433..39.433 rows=0 loops=1)

  • Buffers: shared hit=42,233
4. 9.102 32.791 ↓ 1,223.4 9,787 1

Nested Loop (cost=2.11..32,330.48 rows=8 width=160) (actual time=0.141..32.791 rows=9,787 loops=1)

  • Buffers: shared hit=42,223
5. 2.117 13.902 ↓ 1,223.4 9,787 1

Nested Loop (cost=1.69..32,326.94 rows=8 width=152) (actual time=0.119..13.902 rows=9,787 loops=1)

  • Buffers: shared hit=12,775
6. 0.811 7.709 ↓ 1,019.0 1,019 1

Nested Loop (cost=1.13..32,304.59 rows=1 width=147) (actual time=0.085..7.709 rows=1,019 loops=1)

  • Buffers: shared hit=7,519
7. 1.312 6.898 ↓ 1,060.0 1,060 1

Nested Loop (cost=1.13..32,303.54 rows=1 width=155) (actual time=0.082..6.898 rows=1,060 loops=1)

  • Buffers: shared hit=6,459
8. 0.556 4.526 ↓ 530.0 1,060 1

Nested Loop (cost=0.84..32,302.84 rows=2 width=155) (actual time=0.068..4.526 rows=1,060 loops=1)

  • Buffers: shared hit=3,279
9. 0.185 1.850 ↓ 1.4 1,060 1

Nested Loop (cost=0.56..32,060.01 rows=733 width=98) (actual time=0.056..1.85 rows=1,060 loops=1)

  • Buffers: shared hit=92
10. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on en_offer_status off_status (cost=0..1.04 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: ((off_status.code)::text = 'ACT'::text)
  • Buffers: shared hit=1
11. 0.178 1.656 ↑ 1.7 1,286 1

Nested Loop (cost=0.56..32,031.5 rows=2,198 width=106) (actual time=0.046..1.656 rows=1,286 loops=1)

  • Buffers: shared hit=91
12. 0.005 0.005 ↑ 1.0 1 1

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

  • Filter: ((off_type.code)::text = 'NBKT'::text)
  • Buffers: shared hit=1
13. 1.473 1.473 ↑ 3.0 1,286 1

Index Only Scan using en_offer_offer_start_dt_idx_dlm on en_offer off (cost=0.56..31,992.29 rows=3,797 width=106) (actual time=0.043..1.473 rows=1,286 loops=1)

  • Index Cond: ((off.offer_type_id = off_type.offer_type_id) 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))
  • Heap Fetches: 1,286
  • Buffers: shared hit=90
14. 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..0.32 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=3,187
15. 1.060 1.060 ↑ 1.0 1 1,060

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

  • 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=3,180
16. 0.000 0.000 ↑ 1.0 1 1,060

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

  • Filter: ((eps.code)::text = 'ACT'::text)
  • Buffers: shared hit=1,060
17. 4.076 4.076 ↑ 48.7 10 1,019

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

  • Index Cond: (off_site.offer_id = off.offer_id)
  • Buffers: shared hit=5,256
18. 9.787 9.787 ↑ 1.0 1 9,787

Index Only Scan using en_promotion_partition_site_site_idx_dlm 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=9,787)

  • Index Cond: (pr_re_site.promo_part_site_id = off_site.promo_part_site_id)
  • Buffers: shared hit=29,448
19. 0.000 0.000 ↓ 2.0 2 9,787

Materialize (cost=0.86..24.65 rows=1 width=16) (actual time=0..0 rows=2 loops=9,787)

  • Buffers: shared hit=10
20. 0.002 0.033 ↓ 2.0 2 1

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

  • Buffers: shared hit=10
21. 0.001 0.016 ↑ 1.0 1 1

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

  • Buffers: shared hit=5
22. 0.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

  • Index Cond: (s.site_id = 457,344)
  • Buffers: shared hit=4
23. 0.004 0.004 ↑ 1.0 2 1

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

  • Filter: ((site_type.code)::text <> 'RED'::text)
  • Buffers: shared hit=1
24. 0.015 0.015 ↑ 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.015 rows=2 loops=1)

  • Index Cond: (sgs.site_id = 457,344)
  • Buffers: shared hit=5
25. 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)
26. 0.000 0.000 ↓ 0.0 0 0

Materialize (cost=0.57..10.26 rows=3 width=36) (never executed)

27. 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..10.24 rows=3 width=36) (never executed)

  • Index Cond: (acc_bas_off.account_id = 630,631,466)
  • Filter: ((acc_bas_off.active)::text = 'Y'::text)
Planning time : 7.384 ms
Execution time : 39.624 ms