explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xMXX

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.566 ↓ 22.0 22 1

Sort (cost=26.07..26.08 rows=1 width=564) (actual time=0.564..0.566 rows=22 loops=1)

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

CTE offers_referred

3. 0.004 0.004 ↑ 1.0 20 1

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

4.          

CTE referred_coupons

5. 0.023 0.519 ↓ 55.0 55 1

Unique (cost=25.67..25.70 rows=1 width=55) (actual time=0.490..0.519 rows=55 loops=1)

6. 0.139 0.496 ↓ 110.0 110 1

Sort (cost=25.67..25.68 rows=1 width=55) (actual time=0.490..0.496 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.067 0.357 ↓ 110.0 110 1

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

8. 0.006 0.052 ↓ 2.0 2 1

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

9. 0.006 0.038 ↓ 2.0 2 1

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

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

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

11. 0.001 0.024 ↑ 2.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.002 0.023 ↑ 2.0 1 1

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

13. 0.011 0.011 ↑ 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.011 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.008 0.008 ↑ 1.0 1 2

Index Scan using coupons_pkey on coupons (cost=0.29..4.82 rows=1 width=8) (actual time=0.003..0.004 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-06 22:04:23.827367'::timestamp without time zone) AND (ends_on >= '2019-06-06 22:04:23.827367'::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.004..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.504 0.542 ↓ 25.0 25 1

CTE Scan on referred_coupons referred_coupons_1 (cost=0.03..0.06 rows=1 width=556) (actual time=0.534..0.542 rows=25 loops=1)

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

Initplan (forCTE Scan)

23. 0.003 0.038 ↑ 1.0 1 1

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

24. 0.035 0.035 ↓ 55.0 55 1

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

25.          

Initplan (forSort)

26. 0.002 0.014 ↑ 1.0 1 1

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

27. 0.012 0.012 ↓ 25.0 25 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.012 rows=25 loops=1)

28. 0.554 0.554 ↓ 22.0 22 1

CTE Scan on referred_coupons_status (cost=0.00..0.02 rows=1 width=564) (actual time=0.551..0.554 rows=22 loops=1)

  • Filter: (number_of_referred_coupons_use <= 0)
  • Rows Removed by Filter: 3
Planning time : 0.830 ms
Execution time : 0.667 ms