explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iI87

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 10.902 ↓ 2.0 2 1

Limit (cost=173.16..173.17 rows=1 width=53) (actual time=10.901..10.902 rows=2 loops=1)

2. 0.033 10.900 ↓ 2.0 2 1

Sort (cost=173.16..173.17 rows=1 width=53) (actual time=10.900..10.900 rows=2 loops=1)

  • Sort Key: user_coupons.id
  • Sort Method: quicksort Memory: 25kB
3. 0.017 10.867 ↓ 2.0 2 1

Nested Loop Left Join (cost=102.30..173.15 rows=1 width=53) (actual time=10.503..10.867 rows=2 loops=1)

  • Filter: ((promotions.per_user_active_times > user_promotion_usages.active_times) OR (user_promotion_usages.id IS NULL))
4. 0.048 2.548 ↓ 2.0 2 1

Nested Loop (cost=102.03..170.18 rows=1 width=57) (actual time=2.204..2.548 rows=2 loops=1)

5. 0.056 2.293 ↓ 11.5 23 1

Nested Loop Left Join (cost=101.75..169.39 rows=2 width=65) (actual time=2.144..2.293 rows=23 loops=1)

  • Join Filter: (user_promotion_daily_usages.promotion_id = user_coupons.promotion_id)
  • Rows Removed by Join Filter: 23
  • Filter: ((user_promotion_daily_usages.id IS NULL) OR (promotions.per_user_daily_active_times > user_promotion_daily_usages.active_times))
  • Rows Removed by Filter: 1
6. 0.809 2.093 ↓ 3.4 24 1

Hash Right Join (cost=101.75..168.23 rows=7 width=69) (actual time=2.058..2.093 rows=24 loops=1)

  • Hash Cond: (eatery_promotions.promotion_id = promotions.id)
7. 0.804 0.804 ↓ 1.0 2,580 1

Seq Scan on eatery_promotions (cost=0.00..56.79 rows=2,579 width=4) (actual time=0.022..0.804 rows=2,580 loops=1)

8. 0.033 0.480 ↓ 3.4 24 1

Hash (cost=101.66..101.66 rows=7 width=69) (actual time=0.480..0.480 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3kB
9. 0.074 0.447 ↓ 3.4 24 1

Nested Loop (cost=4.73..101.66 rows=7 width=69) (actual time=0.222..0.447 rows=24 loops=1)

10. 0.178 0.217 ↓ 3.2 39 1

Bitmap Heap Scan on user_coupons (cost=4.45..9.85 rows=12 width=53) (actual time=0.170..0.217 rows=39 loops=1)

  • Recheck Cond: (user_id = 9,552,678)
  • Filter: ((limit_phone_number IS NULL) OR ((limit_phone_number)::text = ''::text) OR ((limit_phone_number)::text = '902772225'::text))
  • Heap Blocks: exact=4
11. 0.039 0.039 ↓ 1.9 44 1

Bitmap Index Scan on user_coupons_user_id_idx (cost=0.00..4.45 rows=23 width=0) (actual time=0.039..0.039 rows=44 loops=1)

  • Index Cond: (user_id = 9,552,678)
12. 0.156 0.156 ↑ 1.0 1 39

Index Scan using promotions_pkey on promotions (cost=0.28..7.64 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=39)

  • Index Cond: (id = user_coupons.promotion_id)
  • Filter: ((active_from < '2020-07-31 15:03:00.686827+07'::timestamp with time zone) AND ('2020-07-31 15:03:00.686827+07'::timestamp with time zone < active_to))
  • Rows Removed by Filter: 0
13. 0.089 0.144 ↑ 1.0 1 24

Materialize (cost=0.00..1.04 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=24)

14. 0.055 0.055 ↑ 1.0 1 1

Seq Scan on user_promotion_daily_usages (cost=0.00..1.04 rows=1 width=12) (actual time=0.054..0.055 rows=1 loops=1)

  • Filter: ((created_at)::date = ('now'::cstring)::date)
  • Rows Removed by Filter: 1
15. 0.207 0.207 ↓ 0.0 0 23

Index Scan using promotion_usages_promotion_id_idx on promotion_usages (cost=0.28..0.38 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=23)

  • Index Cond: (promotion_id = promotions.id)
  • Filter: (promotions.max_active_times > active_times)
  • Rows Removed by Filter: 1
16. 8.302 8.302 ↑ 1.0 1 2

Index Scan using user_promotion_usages_promotion_id_idx on user_promotion_usages (cost=0.27..2.96 rows=1 width=12) (actual time=4.148..4.151 rows=1 loops=2)

  • Index Cond: (promotion_id = user_coupons.promotion_id)
Planning time : 71.009 ms
Execution time : 11.223 ms