explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z1Oh : MFR

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 0.604 ↓ 17.0 17 1

Unique (cost=91.94..91.95 rows=1 width=480) (actual time=0.598..0.604 rows=17 loops=1)

2.          

Initplan (forUnique)

3. 0.000 0.000 ↓ 0.0 0

Index Scan using merchants_pkey on merchants merchants_1 (cost=0.27..8.29 rows=1 width=1) (never executed)

  • Index Cond: (id = 264027)
4. 0.050 0.599 ↓ 27.0 27 1

Sort (cost=83.65..83.65 rows=1 width=480) (actual time=0.597..0.599 rows=27 loops=1)

  • Sort Key: claims.id, (COALESCE((('7917.511728464'::double precision * asin(sqrt((power(sin((((((42.29 - locations.lat))::double precision * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision) + (('0.739748546555466'::double precision * cos((((locations.lat)::double precision * '3.14159265358979'::double precision) / '180'::double precision))) * power(sin(((((('-70.478'::numeric - locations.lng))::double precision * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision)))))))::numeric, 0.0))
  • Sort Method: quicksort Memory: 33kB
5. 0.095 0.549 ↓ 27.0 27 1

Nested Loop Left Join (cost=21.02..83.64 rows=1 width=480) (actual time=0.130..0.549 rows=27 loops=1)

  • Join Filter: (locations.id = apps_hidden_locations.location_id)
  • Filter: (((apps_hidden_locations.id IS NULL) AND locations.visible AND (NOT locations.terminated) AND (NOT merchants.terminated) AND merchants.live) OR campaigns.global)
  • Rows Removed by Filter: 5
6. 0.011 0.454 ↓ 1.1 32 1

Nested Loop Left Join (cost=21.02..81.80 rows=30 width=436) (actual time=0.110..0.454 rows=32 loops=1)

7. 0.017 0.273 ↓ 17.0 17 1

Nested Loop Left Join (cost=14.65..42.95 rows=1 width=390) (actual time=0.092..0.273 rows=17 loops=1)

8. 0.015 0.239 ↓ 17.0 17 1

Nested Loop Left Join (cost=14.36..42.60 rows=1 width=390) (actual time=0.085..0.239 rows=17 loops=1)

9. 0.003 0.207 ↓ 17.0 17 1

Nested Loop Left Join (cost=14.22..42.44 rows=1 width=386) (actual time=0.077..0.207 rows=17 loops=1)

10. 0.038 0.170 ↓ 17.0 17 1

Nested Loop Left Join (cost=13.95..42.12 rows=1 width=382) (actual time=0.068..0.170 rows=17 loops=1)

  • Filter: ((campaigns.redeemable_with_any_app OR (hashed SubPlan 1)) AND ((campaigns.global AND (NOT $1)) OR (hashed SubPlan 3) OR (hashed SubPlan 4) OR (hashed SubPlan 5)))
11. 0.026 0.026 ↓ 17.0 17 1

Index Scan using index_claims_on_user_id on claims (cost=0.43..20.54 rows=1 width=232) (actual time=0.013..0.026 rows=17 loops=1)

  • Index Cond: (user_id = 11211162)
  • Filter: ((NOT transfer_required) AND (value_remaining_amount > 0) AND ((NOT one_item_redemption_limit) OR (value_remaining_amount = value_amount)) AND ((percentage_off IS NULL) OR (value_remaining_amount = value_amount)) AND (expires_at > clock_timestamp()))
12. 0.017 0.085 ↑ 1.0 1 17

Nested Loop Left Join (cost=0.70..8.74 rows=1 width=151) (actual time=0.005..0.005 rows=1 loops=17)

13. 0.034 0.034 ↑ 1.0 1 17

Index Scan using cohorts_pkey on cohorts (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (id = claims.cohort_id)
14. 0.034 0.034 ↑ 1.0 1 17

Index Scan using campaigns_pkey on campaigns (cost=0.28..0.30 rows=1 width=147) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (id = cohorts.campaign_id)
15.          

SubPlan (forNested Loop Left Join)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaigns_redeemable_with_apps (cost=0.00..1.29 rows=1 width=4) (never executed)

  • Filter: (app_id = 75001)
17. 0.012 0.012 ↓ 8.0 8 1

Index Only Scan using index_campaigns_merchants_on_merchant_id_and_campaign_id on campaigns_merchants campaigns_merchants_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.010..0.012 rows=8 loops=1)

  • Index Cond: (merchant_id = 264027)
  • Heap Fetches: 8
18. 0.009 0.009 ↓ 5.3 16 1

Seq Scan on campaigns_targeted_locations campaigns_targeted_locations_1 (cost=0.00..1.12 rows=3 width=4) (actual time=0.006..0.009 rows=16 loops=1)

  • Filter: (location_id = ANY ('{314510,314482,314511}'::integer[]))
  • Rows Removed by Filter: 9
19. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.00..2.10 rows=1 width=4) (never executed)

  • Join Filter: (campaigns_targeted_location_groups.location_group_id = location_group_locations.location_group_id)
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaigns_targeted_location_groups (cost=0.00..1.01 rows=1 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on location_group_locations (cost=0.00..1.05 rows=3 width=4) (never executed)

  • Filter: (location_id = ANY ('{314510,314482,314511}'::integer[]))
22. 0.034 0.034 ↓ 0.0 0 17

Index Only Scan using index_campaigns_merchants_on_campaign_id_and_merchant_id on campaigns_merchants (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.002 rows=0 loops=17)

  • Index Cond: (campaign_id = campaigns.id)
  • Heap Fetches: 5
23. 0.017 0.017 ↑ 1.0 1 17

Index Scan using index_campaigns_targeted_locations_on_campaign_id on campaigns_targeted_locations (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=17)

  • Index Cond: (campaign_id = campaigns.id)
24. 0.017 0.017 ↓ 0.0 0 17

Index Scan using index_gift_cards_on_cohort_id on gift_cards (cost=0.29..0.34 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=17)

  • Index Cond: (cohort_id = cohorts.id)
25. 0.055 0.170 ↑ 5.5 2 17

Nested Loop Left Join (cost=6.37..38.73 rows=11 width=58) (actual time=0.008..0.010 rows=2 loops=17)

26. 0.017 0.051 ↑ 5.5 2 17

Bitmap Heap Scan on locations (cost=6.10..35.29 rows=11 width=42) (actual time=0.003..0.003 rows=2 loops=17)

  • Recheck Cond: ((merchant_id = campaigns_merchants.merchant_id) OR (id = campaigns_targeted_locations.location_id))
  • Heap Blocks: exact=17
27. 0.000 0.034 ↓ 0.0 0 17

BitmapOr (cost=6.10..6.10 rows=11 width=0) (actual time=0.002..0.002 rows=0 loops=17)

28. 0.017 0.017 ↑ 10.0 1 17

Bitmap Index Scan on index_locations_on_merchant_id (cost=0.00..0.44 rows=10 width=0) (actual time=0.001..0.001 rows=1 loops=17)

  • Index Cond: (merchant_id = campaigns_merchants.merchant_id)
29. 0.017 0.017 ↑ 1.0 1 17

Bitmap Index Scan on locations_pkey (cost=0.00..3.40 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=17)

  • Index Cond: (id = campaigns_targeted_locations.location_id)
30. 0.064 0.064 ↑ 1.0 1 32

Index Scan using merchants_pkey on merchants (cost=0.27..0.31 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=32)

  • Index Cond: (id = locations.merchant_id)
31. 0.000 0.000 ↓ 0.0 0 32

Materialize (cost=0.00..1.33 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=32)

32. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on apps_hidden_locations (cost=0.00..1.32 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (app_id = 75001)
  • Rows Removed by Filter: 26