explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F7O2

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 4.418 ↑ 1.0 1 1

Aggregate (cost=173.16..173.17 rows=1 width=0) (actual time=4.418..4.418 rows=1 loops=1)

2. 0.013 4.391 ↓ 2.0 2 1

Nested Loop Left Join (cost=102.30..173.15 rows=1 width=0) (actual time=4.054..4.391 rows=2 loops=1)

  • Filter: ((promotions.per_user_active_times > user_promotion_usages.active_times) OR (user_promotion_usages.id IS NULL))
3. 0.049 4.320 ↓ 2.0 2 1

Nested Loop (cost=102.03..170.19 rows=1 width=8) (actual time=3.998..4.320 rows=2 loops=1)

4. 0.060 3.949 ↓ 11.5 23 1

Nested Loop Left Join (cost=101.75..169.39 rows=2 width=16) (actual time=3.863..3.949 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
5. 2.128 3.769 ↓ 3.4 24 1

Hash Right Join (cost=101.75..168.23 rows=7 width=20) (actual time=3.744..3.769 rows=24 loops=1)

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

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

7. 0.020 0.457 ↓ 3.4 24 1

Hash (cost=101.66..101.66 rows=7 width=20) (actual time=0.457..0.457 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
8. 0.046 0.437 ↓ 3.4 24 1

Nested Loop (cost=4.73..101.66 rows=7 width=20) (actual time=0.185..0.437 rows=24 loops=1)

9. 0.106 0.157 ↓ 3.2 39 1

Bitmap Heap Scan on user_coupons (cost=4.45..9.85 rows=12 width=8) (actual time=0.104..0.157 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
10. 0.051 0.051 ↓ 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.051..0.051 rows=44 loops=1)

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

Index Scan using promotions_pkey on promotions (cost=0.28..7.64 rows=1 width=16) (actual time=0.006..0.006 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
12. 0.030 0.120 ↑ 1.0 1 24

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

13. 0.090 0.090 ↑ 1.0 1 1

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

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

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

  • Index Cond: (promotion_id = promotions.id)
  • Filter: (promotions.max_active_times > active_times)
  • Rows Removed by Filter: 1
15. 0.058 0.058 ↑ 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=0.028..0.029 rows=1 loops=2)

  • Index Cond: (promotion_id = user_coupons.promotion_id)
Planning time : 10.476 ms
Execution time : 4.842 ms