explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UDeB

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 0.605 ↑ 1.0 1 1

Aggregate (cost=26.03..26.04 rows=1 width=8) (actual time=0.605..0.605 rows=1 loops=1)

2.          

CTE offers_referred

3. 0.006 0.006 ↑ 1.0 20 1

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

4.          

CTE referred_coupons

5. 0.023 0.562 ↓ 55.0 55 1

Unique (cost=25.67..25.70 rows=1 width=55) (actual time=0.533..0.562 rows=55 loops=1)

6. 0.153 0.539 ↓ 110.0 110 1

Sort (cost=25.67..25.68 rows=1 width=55) (actual time=0.533..0.539 rows=110 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: 40kB
7. 0.077 0.386 ↓ 110.0 110 1

Nested Loop (cost=10.94..25.66 rows=1 width=55) (actual time=0.080..0.386 rows=110 loops=1)

8. 0.005 0.071 ↓ 2.0 2 1

Nested Loop (cost=10.52..15.54 rows=1 width=31) (actual time=0.058..0.071 rows=2 loops=1)

9. 0.017 0.056 ↓ 2.0 2 1

Hash Join (cost=10.23..10.71 rows=1 width=31) (actual time=0.048..0.056 rows=2 loops=1)

  • Hash Cond: (offers_referred.coupon_id = coupon_codes.coupon_id)
10. 0.011 0.011 ↑ 1.0 20 1

CTE Scan on offers_referred (cost=0.00..0.40 rows=20 width=8) (actual time=0.004..0.011 rows=20 loops=1)

11. 0.003 0.028 ↑ 2.0 1 1

Hash (cost=10.20..10.20 rows=2 width=23) (actual time=0.028..0.028 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.005 0.025 ↑ 2.0 1 1

Nested Loop (cost=0.86..10.20 rows=2 width=23) (actual time=0.024..0.025 rows=1 loops=1)

13. 0.010 0.010 ↑ 2.0 1 1

Index Scan using application_user_referred_codes_copy_application_user_id_idx on application_user_referred_codes (cost=0.43..4.88 rows=2 width=15) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (application_user_id = 1912488163)
  • Filter: is_active
  • Rows Removed by Filter: 2
14. 0.010 0.010 ↑ 1.0 1 1

Index Scan using coupon_codes_code_unique on coupon_codes (cost=0.43..2.65 rows=1 width=18) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((code)::text = (application_user_referred_codes.referred_code)::text)
15. 0.010 0.010 ↑ 1.0 1 2

Index Scan using coupons_pkey on coupons (cost=0.29..4.82 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=2)

  • Index Cond: ((id = coupon_codes.coupon_id) AND (id = ANY ('{84990,84413,2,84256,84415,97703,97774,97737,97669,97667,97665,97664,80391,80390,84234,84301}'::integer[])))
  • Filter: ((starts_on <= '2019-06-05 13:31:54.268847'::timestamp without time zone) AND (ends_on >= '2019-06-05 13:31:54.268847'::timestamp without time zone))
16. 0.018 0.018 ↑ 1.0 55 2

Index Only Scan using application_user_coupons_updated_at on application_user_coupons user_coupons (cost=0.42..1.41 rows=56 width=24) (actual time=0.005..0.009 rows=55 loops=2)

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

SubPlan (forNested Loop)

18. 0.000 0.220 ↑ 1.0 1 110

Aggregate (cost=8.15..8.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=110)

19. 0.220 0.220 ↑ 4.0 1 110

Index Scan using application_user_coupons_copy_application_user_id_coupon_co_key on application_user_coupons (cost=0.42..8.14 rows=4 width=0) (actual time=0.002..0.002 rows=1 loops=110)

  • Index 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: 2
20.          

CTE referred_coupons_status

21. 0.546 0.590 ↓ 30.0 30 1

CTE Scan on referred_coupons referred_coupons_1 (cost=0.03..0.06 rows=1 width=556) (actual time=0.582..0.590 rows=30 loops=1)

  • Filter: ((referred_coupon_type_id = $7) AND (status = 2))
  • Rows Removed by Filter: 25
22.          

Initplan (forCTE Scan)

23. 0.003 0.044 ↑ 1.0 1 1

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

24. 0.041 0.041 ↓ 55.0 55 1

CTE Scan on referred_coupons (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.041 rows=55 loops=1)

25. 0.600 0.600 ↓ 24.0 24 1

CTE Scan on referred_coupons_status (cost=0.00..0.02 rows=1 width=4) (actual time=0.585..0.600 rows=24 loops=1)

  • Filter: (number_of_referred_coupons_use <= 0)
  • Rows Removed by Filter: 6
Planning time : 0.959 ms
Execution time : 0.801 ms