explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 176

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 30,848.430 ↓ 24.0 24 1

Sort (cost=5,728.26..5,728.27 rows=1 width=564) (actual time=30,848.423..30,848.430 rows=24 loops=1)

  • Sort Key: referred_coupons_status.updated_at DESC
  • Sort Method: quicksort Memory: 28kB
2.          

CTE offers_referred

3. 0.014 0.014 ↑ 1.0 20 1

Values Scan on "*VALUES*" (cost=0.00..0.25 rows=20 width=8) (actual time=0.003..0.014 rows=20 loops=1)

4.          

CTE referred_coupons

5. 0.063 30,848.291 ↓ 60.0 60 1

Unique (cost=5,727.86..5,727.89 rows=1 width=556) (actual time=30,848.205..30,848.291 rows=60 loops=1)

6. 0.259 30,848.228 ↓ 120.0 120 1

Sort (cost=5,727.86..5,727.87 rows=1 width=556) (actual time=30,848.204..30,848.228 rows=120 loops=1)

  • Sort Key: application_user_referred_codes.referred_code, coupon_codes.coupon_id, coupons.referred_coupon_type_id, user_coupons.application_user_id, user_coupons.status, user_coupons.updated_at, offers_referred.value, ((SubPlan 2))
  • Sort Method: quicksort Memory: 41kB
7. 0.148 30,847.969 ↓ 120.0 120 1

Nested Loop (cost=146.19..5,727.85 rows=1 width=556) (actual time=81.316..30,847.969 rows=120 loops=1)

8. 1,351.365 30,847.123 ↓ 2.0 2 1

Nested Loop (cost=145.76..2,921.21 rows=1 width=532) (actual time=81.274..30,847.123 rows=2 loops=1)

9. 2,548.888 4,352.303 ↓ 5,028,691.0 5,028,691 1

Nested Loop (cost=145.34..2,868.46 rows=1 width=532) (actual time=80.995..4,352.303 rows=5,028,691 loops=1)

  • Join Filter: (coupons.id = coupon_codes.coupon_id)
10. 0.025 0.165 ↓ 5.0 5 1

Nested Loop (cost=0.29..41.34 rows=1 width=16) (actual time=0.033..0.165 rows=5 loops=1)

  • Join Filter: (coupons.id = offers_referred.coupon_id)
  • Rows Removed by Join Filter: 75
11. 0.076 0.076 ↓ 2.0 4 1

Index Scan using coupons_pkey on coupons (cost=0.29..40.24 rows=2 width=8) (actual time=0.021..0.076 rows=4 loops=1)

  • Index Cond: (id = ANY ('{84990,84413,2,84256,84415,97703,97774,97737,97669,97667,97665,97664,80391,80390,84234,84301}'::integer[]))
  • Filter: ((starts_on <= '2019-06-06 22:01:03.893139'::timestamp without time zone) AND (ends_on >= '2019-06-06 22:01:03.893139'::timestamp without time zone))
  • Rows Removed by Filter: 12
12. 0.064 0.064 ↑ 1.0 20 4

CTE Scan on offers_referred (cost=0.00..0.40 rows=20 width=8) (actual time=0.001..0.016 rows=20 loops=4)

13. 1,483.710 1,803.250 ↓ 247.5 1,005,738 5

Bitmap Heap Scan on coupon_codes (cost=145.05..2,776.34 rows=4,063 width=524) (actual time=66.238..360.650 rows=1,005,738 loops=5)

  • Recheck Cond: (coupon_id = offers_referred.coupon_id)
  • Heap Blocks: exact=70203
14. 319.540 319.540 ↓ 247.5 1,005,739 5

Bitmap Index Scan on coupon_codes_coupon_id_index (cost=0.00..144.03 rows=4,063 width=0) (actual time=63.908..63.908 rows=1,005,739 loops=5)

  • Index Cond: (coupon_id = offers_referred.coupon_id)
15. 25,143.455 25,143.455 ↓ 0.0 0 5,028,691

Index Scan using application_user_referred_codes_referred_code_index on application_user_referred_codes (cost=0.42..52.68 rows=7 width=516) (actual time=0.005..0.005 rows=0 loops=5,028,691)

  • Index Cond: ((referred_code)::text = (coupon_codes.code)::text)
  • Filter: (is_active AND (application_user_id = 1912488163))
  • Rows Removed by Filter: 0
16. 0.098 0.098 ↑ 55.0 60 2

Index Only Scan using application_user_coupons_updated_at on application_user_coupons user_coupons (cost=0.42..58.21 rows=3,301 width=24) (actual time=0.011..0.049 rows=60 loops=2)

  • Index Cond: (coupon_code_id = coupon_codes.id)
  • Heap Fetches: 120
17.          

SubPlan (forNested Loop)

18. 0.120 0.600 ↑ 1.0 1 120

Aggregate (cost=2,715.41..2,715.42 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=120)

19. 0.240 0.480 ↓ 0.0 0 120

Bitmap Heap Scan on application_user_coupons (cost=36.19..2,715.37 rows=16 width=0) (actual time=0.004..0.004 rows=0 loops=120)

  • Recheck Cond: (application_user_id = user_coupons.application_user_id)
  • Filter: ((coupon_code_id <> user_coupons.coupon_code_id) AND (status = 2))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=130
20. 0.240 0.240 ↑ 1,650.5 2 120

Bitmap Index Scan on application_user_coupons_application_user_id (cost=0.00..36.18 rows=3,301 width=0) (actual time=0.002..0.002 rows=2 loops=120)

  • Index Cond: (application_user_id = user_coupons.application_user_id)
21.          

CTE referred_coupons_status

22. 30,848.240 30,848.369 ↓ 26.0 26 1

CTE Scan on referred_coupons referred_coupons_1 (cost=0.03..0.06 rows=1 width=556) (actual time=30,848.354..30,848.369 rows=26 loops=1)

  • Filter: ((status = ANY ('{1,3}'::integer[])) AND (referred_coupon_type_id = $7))
  • Rows Removed by Filter: 34
23.          

Initplan (forCTE Scan)

24. 0.019 0.129 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.128..0.129 rows=1 loops=1)

25. 0.110 0.110 ↓ 60.0 60 1

CTE Scan on referred_coupons (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.110 rows=60 loops=1)

26.          

Initplan (forSort)

27. 0.013 0.038 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.037..0.038 rows=1 loops=1)

28. 0.025 0.025 ↓ 26.0 26 1

CTE Scan on referred_coupons_status referred_coupons_status_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.025 rows=26 loops=1)

29. 30,848.406 30,848.406 ↓ 24.0 24 1

CTE Scan on referred_coupons_status (cost=0.00..0.02 rows=1 width=564) (actual time=30,848.397..30,848.406 rows=24 loops=1)

  • Filter: (number_of_referred_coupons_use <= 0)
  • Rows Removed by Filter: 2
Planning time : 0.836 ms
Execution time : 30,848.649 ms