explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FUAa

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

Nested Loop Left Join (cost=3,607.08..35,254.95 rows=1 width=191) (actual time=77.387..77.387 rows=0 loops=1)

  • Join Filter: (acc_bas_off.basket_offer_id = bas_off.basket_offer_id)
2. 0.000 77.386 ↓ 0.0 0 1

Nested Loop (cost=3,606.51..35,251.86 rows=1 width=159) (actual time=77.386..77.386 rows=0 loops=1)

3. 1.486 77.386 ↓ 0.0 0 1

Nested Loop (cost=3,606.07..35,249.49 rows=1 width=151) (actual time=77.386..77.386 rows=0 loops=1)

  • Join Filter: ((sgs.site_group_id = pr_re_site.site_group_id) OR (s.site_id = pr_re_site.site_id))
  • Rows Removed by Join Filter: 19574
4. 0.001 0.068 ↓ 2.0 2 1

Nested Loop Left Join (cost=0.86..23,213.21 rows=1 width=16) (actual time=0.064..0.068 rows=2 loops=1)

  • Join Filter: (s.site_id = sgs.site_id)
5. 0.003 0.056 ↑ 1.0 1 1

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

  • Join Filter: (s.site_type_id = site_type.site_type_id)
  • Rows Removed by Join Filter: 1
6. 0.049 0.049 ↑ 1.0 1 1

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

  • Index Cond: (site_id = 457344)
7. 0.004 0.004 ↑ 1.0 2 1

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

  • Filter: ((code)::text <> 'RED'::text)
  • Rows Removed by Filter: 1
8. 0.011 0.011 ↑ 15,454.5 2 1

Index Scan using en_site_gr_siteid_idx on en_site_group_site sgs (cost=0.43..22,823.34 rows=30,909 width=16) (actual time=0.008..0.011 rows=2 loops=1)

  • Index Cond: (site_id = 457344)
9. 20.644 75.832 ↓ 1,223.4 9,787 2

Nested Loop (cost=3,605.21..12,036.16 rows=8 width=159) (actual time=3.318..37.916 rows=9,787 loops=2)

10. 3.478 35.614 ↓ 1,223.4 9,787 2

Nested Loop (cost=3,604.79..12,032.62 rows=8 width=151) (actual time=3.305..17.807 rows=9,787 loops=2)

11. 1.120 21.946 ↓ 1,019.0 1,019 2

Nested Loop (cost=3,604.35..8,709.19 rows=1 width=146) (actual time=3.273..10.973 rows=1,019 loops=2)

12. 2.452 18.706 ↓ 1,060.0 1,060 2

Nested Loop (cost=3,604.22..8,709.02 rows=1 width=154) (actual time=3.265..9.353 rows=1,060 loops=2)

13. 0.718 14.134 ↓ 353.3 1,060 2

Nested Loop (cost=3,603.93..8,708.07 rows=3 width=154) (actual time=3.255..7.067 rows=1,060 loops=2)

14. 0.481 9.176 ↓ 1.1 1,060 2

Hash Join (cost=3,603.65..8,413.39 rows=926 width=97) (actual time=3.244..4.588 rows=1,060 loops=2)

  • Hash Cond: (off.offer_status_id = off_status.offer_status_id)
15. 0.546 8.688 ↑ 2.2 1,286 2

Nested Loop (cost=3,602.60..8,394.75 rows=2,777 width=105) (actual time=3.220..4.344 rows=1,286 loops=2)

16. 0.012 0.012 ↑ 1.0 1 2

Seq Scan on en_offer_type off_type (cost=0.00..1.24 rows=1 width=8) (actual time=0.004..0.006 rows=1 loops=2)

  • Filter: ((code)::text = 'NBKT'::text)
  • Rows Removed by Filter: 18
17. 1.908 8.130 ↑ 3.7 1,286 2

Bitmap Heap Scan on en_offer off (cost=3,602.60..8,345.54 rows=4,797 width=105) (actual time=3.196..4.065 rows=1,286 loops=2)

  • Recheck Cond: (('2020-01-27 17:32:15.312'::timestamp without time zone <= offer_end_dt) AND ('2020-01-27 17:32:15.312'::timestamp without time zone >= offer_start_dt) AND (offer_type_id = off_type.offer_type_id))
  • Heap Blocks: exact=1330
18. 0.638 6.222 ↓ 0.0 0 2

BitmapAnd (cost=3,602.60..3,602.60 rows=4,797 width=0) (actual time=3.111..3.111 rows=0 loops=2)

19. 5.250 5.250 ↑ 1.4 37,805 2

Bitmap Index Scan on en_offer_dt_idx (cost=0.00..736.65 rows=52,764 width=0) (actual time=2.625..2.625 rows=37,805 loops=2)

  • Index Cond: (('2020-01-27 17:32:15.312'::timestamp without time zone <= offer_end_dt) AND ('2020-01-27 17:32:15.312'::timestamp without time zone >= offer_start_dt))
20. 0.334 0.334 ↑ 168.5 1,651 2

Bitmap Index Scan on en_off_offer_type_id_idx (cost=0.00..2,851.30 rows=278,250 width=0) (actual time=0.167..0.167 rows=1,651 loops=2)

  • Index Cond: (offer_type_id = off_type.offer_type_id)
21. 0.003 0.007 ↑ 1.0 1 1

Hash (cost=1.04..1.04 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on en_offer_status off_status (cost=0.00..1.04 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)

  • Filter: ((code)::text = 'ACT'::text)
  • Rows Removed by Filter: 2
23. 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.31 rows=1 width=57) (actual time=0.001..0.002 rows=1 loops=2,120)

  • Index Cond: (offer_id = off.offer_id)
  • Filter: (is_bonus = 'N'::bpchar)
24. 2.120 2.120 ↑ 1.0 1 2,120

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

  • Index Cond: (promotion_id = off.promotion_id)
  • Filter: (('2020-01-27 17:32:15.312'::timestamp without time zone >= promotion_start_dt) AND ('2020-01-27 17:32:15.312'::timestamp without time zone <= promotion_end_dt))
25. 2.120 2.120 ↑ 1.0 1 2,120

Index Scan using en_promotion_status_pk on en_promotion_status eps (cost=0.13..0.15 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,120)

  • Index Cond: (promotion_status_id = ep.promotion_status_id)
  • Filter: ((code)::text = 'ACT'::text)
  • Rows Removed by Filter: 0
26. 10.190 10.190 ↑ 12,065.2 10 2,038

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

  • Index Cond: (offer_id = off.offer_id)
27. 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: (promo_part_site_id = off_site.promo_part_site_id)
28. 0.000 0.000 ↓ 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: (reward_id = bas_off.reward_id)
29. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (account_id = 630631466)
  • Filter: ((active)::text = 'Y'::text)
Planning time : 7.785 ms
Execution time : 77.763 ms