explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tBhS : staging target geo without index

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.421 4.870 ↓ 11.5 23 1

Sort (cost=567.28..567.28 rows=2 width=5,170) (actual time=4.850..4.870 rows=23 loops=1)

  • Sort Key: selected_offers.previous_offer_id, apps.vip DESC, selected_offers.number, offer_steps.order_index
  • Sort Method: quicksort Memory: 36kB
2. 0.068 4.449 ↓ 11.5 23 1

Nested Loop Left Join (cost=551.13..567.27 rows=2 width=5,170) (actual time=3.837..4.449 rows=23 loops=1)

3. 0.042 4.304 ↓ 11.0 11 1

Nested Loop Left Join (cost=550.85..558.90 rows=1 width=5,163) (actual time=3.807..4.304 rows=11 loops=1)

4. 4.196 4.196 ↓ 11.0 11 1

CTE Scan on selected_offers (cost=550.57..550.59 rows=1 width=5,227) (actual time=3.796..4.196 rows=11 loops=1)

5.          

CTE selected_offers

6. 0.072 4.114 ↓ 11.0 11 1

Nested Loop Left Join (cost=410.64..550.57 rows=1 width=960) (actual time=3.782..4.114 rows=11 loops=1)

  • Filter: (((offers.reward_after)::text = 'prepared_review_creation'::text) OR (((offers.reward_after)::text <> 'prepared_review_creation'::text) AND (completed_offers_2.id IS NULL)))
7. 1.148 3.888 ↓ 11.0 11 1

Index Scan using index_offers_on_iphone_offerwal_options_partial on offers (cost=341.19..477.09 rows=1 width=960) (actual time=3.748..3.888 rows=11 loops=1)

  • Index Cond: (min_user_installs <= 0)
  • Filter: ((target_gender IS NULL) AND ((started_at <= '2020-06-29 08:43:35.178062'::timestamp without time zone) OR (started_at IS NULL)) 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 ((target_geo IS NULL) OR ((target_geo)::text = 'MOS'::text)) 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))))
  • Rows Removed by Filter: 4
8.          

SubPlan (for Index Scan)

9. 0.005 0.015 ↓ 0.0 0 1

Bitmap Heap Scan on callibration_reviews (cost=4.17..11.28 rows=3 width=4) (actual time=0.015..0.015 rows=0 loops=1)

  • Recheck Cond: (user_id = 198,336)
10. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on index_callibration_reviews_on_user_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.009..0.010 rows=0 loops=1)

  • Index Cond: (user_id = 198,336)
11. 0.001 0.036 ↓ 0.0 0 1

Bitmap Heap Scan on completed_offers (cost=9.03..97.39 rows=23 width=4) (actual time=0.036..0.036 rows=0 loops=1)

  • Recheck Cond: ((device_id = 878) OR (user_id = 198,336))
  • Filter: ((rewarded AND (device_id = 878)) OR ((user_id = 198,336) AND (device_id IS NULL)))
12. 0.003 0.035 ↓ 0.0 0 1

BitmapOr (cost=9.03..9.03 rows=23 width=0) (actual time=0.034..0.035 rows=0 loops=1)

13. 0.015 0.015 ↓ 0.0 0 1

Bitmap Index Scan on index_completed_offers_on_device_id (cost=0.00..4.47 rows=6 width=0) (actual time=0.014..0.015 rows=0 loops=1)

  • Index Cond: (device_id = 878)
14. 0.017 0.017 ↓ 0.0 0 1

Bitmap Index Scan on index_completed_offers_on_user_id (cost=0.00..4.55 rows=17 width=0) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: (user_id = 198,336)
15. 0.003 0.015 ↓ 0.0 0 1

Bitmap Heap Scan on completed_offers completed_offers_1 (cost=9.03..97.39 rows=23 width=4) (actual time=0.014..0.015 rows=0 loops=1)

  • Recheck Cond: ((device_id = 878) OR (user_id = 198,336))
  • Filter: ((rewarded AND (device_id = 878)) OR ((user_id = 198,336) AND (device_id IS NULL)))
16. 0.003 0.012 ↓ 0.0 0 1

BitmapOr (cost=9.03..9.03 rows=23 width=0) (actual time=0.012..0.012 rows=0 loops=1)

17. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on index_completed_offers_on_device_id (cost=0.00..4.47 rows=6 width=0) (actual time=0.004..0.005 rows=0 loops=1)

  • Index Cond: (device_id = 878)
18. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on index_completed_offers_on_user_id (cost=0.00..4.55 rows=17 width=0) (actual time=0.003..0.004 rows=0 loops=1)

  • Index Cond: (user_id = 198,336)
19. 0.014 0.030 ↓ 0.0 0 10

Materialize (cost=0.28..10.70 rows=2 width=4) (actual time=0.003..0.003 rows=0 loops=10)

20. 0.016 0.016 ↓ 0.0 0 1

Index Only Scan using index_installed_apps_on_device_id_and_app_id on installed_apps (cost=0.28..10.69 rows=2 width=4) (actual time=0.015..0.016 rows=0 loops=1)

  • Index Cond: (device_id = 878)
  • Heap Fetches: 0
21. 1.681 2.644 ↓ 130.3 782 1

Hash Left Join (cost=10.29..123.98 rows=6 width=4) (actual time=0.046..2.644 rows=782 loops=1)

  • Hash Cond: (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)))
22. 0.950 0.950 ↓ 1.0 782 1

Seq Scan on apps apps_1 (cost=0.00..109.78 rows=778 width=5) (actual time=0.010..0.950 rows=782 loops=1)

23. 0.002 0.013 ↓ 0.0 0 1

Hash (cost=10.26..10.26 rows=2 width=4) (actual time=0.012..0.013 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
24. 0.002 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on installed_apps installed_apps_1 (cost=4.29..10.26 rows=2 width=4) (actual time=0.010..0.011 rows=0 loops=1)

  • Recheck Cond: (user_id = 198,336)
25. 0.009 0.009 ↓ 0.0 0 1

Bitmap Index Scan on index_installed_apps_on_user_id_and_app_id (cost=0.00..4.29 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (user_id = 198,336)
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_installed_apps_on_user_id_and_app_id on installed_apps installed_apps_2 (cost=0.28..10.60 rows=2 width=4) (never executed)

  • Index Cond: (user_id = 198,336)
  • Heap Fetches: 0
27. 0.022 0.154 ↓ 0.0 0 11

Bitmap Heap Scan on completed_offers completed_offers_2 (cost=69.45..73.47 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=11)

  • Recheck Cond: (((device_id = 878) OR (user_id = 198,336)) AND (offer_id = offers.id))
  • Filter: ((device_id = 878) OR ((user_id = 198,336) AND (device_id IS NULL)))
28. 0.022 0.132 ↓ 0.0 0 11

BitmapAnd (cost=69.45..69.45 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=11)

29. 0.011 0.110 ↓ 0.0 0 11

BitmapOr (cost=9.03..9.03 rows=23 width=0) (actual time=0.010..0.010 rows=0 loops=11)

30. 0.055 0.055 ↓ 0.0 0 11

Bitmap Index Scan on index_completed_offers_on_device_id (cost=0.00..4.47 rows=6 width=0) (actual time=0.005..0.005 rows=0 loops=11)

  • Index Cond: (device_id = 878)
31. 0.044 0.044 ↓ 0.0 0 11

Bitmap Index Scan on index_completed_offers_on_user_id (cost=0.00..4.55 rows=17 width=0) (actual time=0.004..0.004 rows=0 loops=11)

  • Index Cond: (user_id = 198,336)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_completed_offers_on_offer_id_and_user_id (cost=0.00..60.16 rows=2,632 width=0) (never executed)

  • Index Cond: (offer_id = offers.id)
33. 0.066 0.066 ↑ 1.0 1 11

Index Scan using apps_pkey on apps (cost=0.28..8.29 rows=1 width=5) (actual time=0.005..0.006 rows=1 loops=11)

  • Index Cond: (id = selected_offers.app_id)
34. 0.077 0.077 ↑ 1.0 2 11

Index Scan using index_offer_steps_on_offer_id on offer_steps (cost=0.28..8.34 rows=2 width=11) (actual time=0.005..0.007 rows=2 loops=11)

  • Index Cond: (offer_id = selected_offers.id)
Planning time : 10.966 ms
Execution time : 5.607 ms