explain.depesz.com

PostgreSQL's explain analyze made readable

Result: daju : target_geo

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.011 0.029 ↓ 0.0 0 1

Sort (cost=104.78..104.79 rows=1 width=5,138) (actual time=0.029..0.029 rows=0 loops=1)

  • Sort Key: offers.previous_offer_id, apps.vip DESC, offers.number, offer_steps.order_index
  • Sort Method: quicksort Memory: 25kB
2. 0.001 0.018 ↓ 0.0 0 1

Nested Loop Left Join (cost=94.65..104.77 rows=1 width=5,138) (actual time=0.018..0.018 rows=0 loops=1)

3. 0.010 0.017 ↓ 0.0 0 1

Hash Right Join (cost=94.51..96.61 rows=1 width=5,137) (actual time=0.017..0.017 rows=0 loops=1)

  • Hash Cond: (offer_steps.offer_id = offers.id)
4. 0.000 0.000 ↓ 0.0 0

Seq Scan on offer_steps (cost=0.00..1.79 rows=79 width=11) (never executed)

5. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=94.50..94.50 rows=1 width=5,130) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
6. 0.001 0.007 ↓ 0.0 0 1

Subquery Scan on offers (cost=94.47..94.50 rows=1 width=5,130) (actual time=0.007..0.007 rows=0 loops=1)

7. 0.006 0.006 ↓ 0.0 0 1

CTE Scan on selected_offers (cost=94.47..94.49 rows=1 width=5,195) (actual time=0.006..0.006 rows=0 loops=1)

8.          

CTE selected_offers

9. 0.000 0.005 ↓ 0.0 0 1

Nested Loop Left Join (cost=76.37..94.47 rows=1 width=3,802) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (((offers_1.reward_after)::text = 'prepared_review_creation'::text) OR (((offers_1.reward_after)::text <> 'prepared_review_creation'::text) AND (completed_offers_2.id IS NULL)))
10. 0.005 0.005 ↓ 0.0 0 1

Index Scan using index_offers_on_offerwal_options_partial on offers offers_1 (cost=76.22..86.28 rows=1 width=3,802) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((iphone = true) AND (rem_day_installs > 0))
  • Filter: (iphone AND (target_gender IS NULL) AND ((started_at <= '2020-06-23 05:58:11.99521'::timestamp without time zone) OR (started_at IS NULL)) AND (min_user_installs <= 0) AND ((max_user_installs > 0) OR (max_user_installs IS NULL)) AND ((review_type = 0) OR (review_type IS NULL)) AND (NOT (hashed SubPlan 1)) AND ((reward_after)::text <> 'prepared_review_publication'::text) AND ((reward_after)::text <> 'prepared_review_creation'::text) AND ((hashed SubPlan 5) OR (previous_offer_id IS NULL)) AND ((NOT (hashed SubPlan 6)) OR (second_chance_offer_id IS NULL)) AND ((country)::text = 'ru'::text) AND ((((reward_after)::text = ANY ('{click,app_postback,retention}'::text[])) AND (SubPlan 2) AND (hashed SubPlan 3)) OR (((reward_after)::text = ANY ('{review,prepared_review_publication,prepared_review_creation,callibration_review,partner_webhook}'::text[])) AND ((NOT for_user_installed_app) OR (for_user_installed_app AND (hashed SubPlan 4)))) OR (((reward_after)::text = ANY ('{click,prepared_review_publication,retention}'::text[])) AND (previous_offer_id IS NOT NULL))))
11.          

SubPlan (for Index Scan)

12. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on callibration_reviews (cost=4.17..11.28 rows=3 width=4) (never executed)

  • Recheck Cond: (user_id = 4,409,346)
13. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_callibration_reviews_on_user_id (cost=0.00..4.17 rows=3 width=0) (never executed)

  • Index Cond: (user_id = 4,409,346)
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on completed_offers (cost=0.00..12.10 rows=1 width=4) (never executed)

  • Filter: ((rewarded AND (device_id = 4,079,681)) OR ((user_id = 4,409,346) AND (device_id IS NULL)))
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on completed_offers completed_offers_1 (cost=0.00..12.10 rows=1 width=4) (never executed)

  • Filter: ((rewarded AND (device_id = 4,079,681)) OR ((user_id = 4,409,346) AND (device_id IS NULL)))
16. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4.18..12.66 rows=4 width=4) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on installed_apps (cost=4.18..12.64 rows=4 width=4) (never executed)

  • Recheck Cond: (device_id = 4,079,681)
18. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_installed_apps_on_device_id_and_app_id (cost=0.00..4.18 rows=4 width=0) (never executed)

  • Index Cond: (device_id = 4,079,681)
19. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=4.18..23.75 rows=8 width=4) (never executed)

  • Join Filter: (apps_1.id = installed_apps_1.app_id)
  • Filter: ((NOT apps_1.only_one_install) OR (apps_1.only_one_install AND (installed_apps_1.app_id IS NULL)))
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on apps apps_1 (cost=0.00..10.20 rows=15 width=5) (never executed)

  • Filter: ((NOT only_one_install) OR only_one_install)
21. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4.18..12.66 rows=4 width=4) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on installed_apps installed_apps_1 (cost=4.18..12.64 rows=4 width=4) (never executed)

  • Recheck Cond: (user_id = 4,409,346)
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_installed_apps_on_user_id_and_app_id (cost=0.00..4.18 rows=4 width=0) (never executed)

  • Index Cond: (user_id = 4,409,346)
24. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on installed_apps installed_apps_2 (cost=4.18..12.64 rows=4 width=4) (never executed)

  • Recheck Cond: (user_id = 4,409,346)
25. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_installed_apps_on_user_id_and_app_id (cost=0.00..4.18 rows=4 width=0) (never executed)

  • Index Cond: (user_id = 4,409,346)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using index_completed_offers_on_offer_id_and_user_id on completed_offers completed_offers_2 (cost=0.14..8.17 rows=1 width=8) (never executed)

  • Index Cond: (offer_id = offers_1.id)
  • Filter: ((device_id = 4,079,681) OR ((user_id = 4,409,346) AND (device_id IS NULL)))
27. 0.000 0.000 ↓ 0.0 0

Index Scan using apps_pkey on apps (cost=0.14..8.15 rows=1 width=5) (never executed)

  • Index Cond: (id = offers.app_id)
Planning time : 2.096 ms
Execution time : 0.293 ms