explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g4II

Settings
# exclusive inclusive rows x rows loops node
1. 0.354 17.136 ↓ 9.5 19 1

Sort (cost=1,099.16..1,099.16 rows=2 width=5,167) (actual time=17.125..17.136 rows=19 loops=1)

  • Sort Key: selected_offers.previous_offer_id, apps.vip DESC, selected_offers.number, offer_steps.order_index
  • Sort Method: quicksort Memory: 61kB
2. 0.016 16.782 ↓ 9.5 19 1

Nested Loop Left Join (cost=1,094.16..1,099.15 rows=2 width=5,167) (actual time=6.690..16.782 rows=19 loops=1)

3. 0.037 16.701 ↓ 13.0 13 1

Nested Loop Left Join (cost=1,093.87..1,096.13 rows=1 width=5,160) (actual time=6.682..16.701 rows=13 loops=1)

4. 16.612 16.612 ↓ 13.0 13 1

CTE Scan on selected_offers (cost=1,093.59..1,093.61 rows=1 width=5,159) (actual time=6.676..16.612 rows=13 loops=1)

5.          

CTE selected_offers

6. 0.051 16.521 ↓ 13.0 13 1

Nested Loop Left Join (cost=877.98..1,093.59 rows=1 width=1,522) (actual time=6.667..16.521 rows=13 loops=1)

  • Filter: (((offers.reward_after)::text = 'prepared_review_creation'::text) OR (((offers.reward_after)::text <> 'prepared_review_creation'::text) AND (completed_offers_1.id IS NULL)))
  • Rows Removed by Filter: 1
7. 1.940 6.880 ↓ 7.0 14 1

Index Scan using index_offers_on_iphone_offerwal_options_partial on offers (cost=849.17..1,033.71 rows=2 width=1,522) (actual time=6.610..6.880 rows=14 loops=1)

  • Index Cond: (min_user_installs <= 7)
  • Filter: (((started_at <= '2020-02-05 13:25:15.697498'::timestamp without time zone) OR (started_at IS NULL)) AND ((max_user_installs > 7) 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 ((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: 48
8.          

SubPlan (for Index Scan)

9. 0.007 0.007 ↓ 0.0 0 1

Index Scan using index_callibration_reviews_on_user_id on callibration_reviews (cost=0.29..2.51 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (user_id = 7552144)
10. 0.029 0.054 ↑ 5.2 13 1

Bitmap Heap Scan on completed_offers (cost=4.29..180.07 rows=67 width=4) (actual time=0.033..0.054 rows=13 loops=1)

  • Recheck Cond: ((device_id = 7221567) OR (user_id = 7552144))
  • Filter: ((rewarded AND (device_id = 7221567)) OR ((user_id = 7552144) AND (device_id IS NULL)))
  • Heap Blocks: exact=9
11. 0.000 0.025 ↓ 0.0 0 1

BitmapOr (cost=4.29..4.29 rows=158 width=0) (actual time=0.025..0.025 rows=0 loops=1)

12. 0.016 0.016 ↑ 5.3 13 1

Bitmap Index Scan on index_completed_offers_on_device_id (cost=0.00..2.06 rows=69 width=0) (actual time=0.016..0.016 rows=13 loops=1)

  • Index Cond: (device_id = 7221567)
13. 0.009 0.009 ↑ 6.8 13 1

Bitmap Index Scan on index_completed_offers_on_user_id (cost=0.00..2.21 rows=89 width=0) (actual time=0.009..0.009 rows=13 loops=1)

  • Index Cond: (user_id = 7552144)
14. 0.027 0.099 ↑ 7.4 9 33

Materialize (cost=0.44..18.20 rows=67 width=4) (actual time=0.001..0.003 rows=9 loops=33)

15. 0.072 0.072 ↑ 5.2 13 1

Index Only Scan using index_installed_apps_on_device_id_and_app_id on installed_apps (cost=0.44..17.87 rows=67 width=4) (actual time=0.011..0.072 rows=13 loops=1)

  • Index Cond: (device_id = 7221567)
  • Heap Fetches: 13
16. 2.482 4.780 ↓ 540.1 4,321 1

Merge Left Join (cost=23.75..644.35 rows=8 width=4) (actual time=0.052..4.780 rows=4,321 loops=1)

  • Merge 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)))
  • Rows Removed by Filter: 10
17. 2.250 2.250 ↑ 1.0 4,331 1

Index Scan using apps_pkey on apps apps_1 (cost=0.28..608.95 rows=4,331 width=5) (actual time=0.004..2.250 rows=4,331 loops=1)

18. 0.021 0.048 ↑ 5.7 13 1

Sort (cost=23.46..23.65 rows=74 width=4) (actual time=0.043..0.048 rows=13 loops=1)

  • Sort Key: installed_apps_1.app_id
  • Sort Method: quicksort Memory: 25kB
19. 0.027 0.027 ↑ 5.7 13 1

Index Only Scan using index_installed_apps_on_user_id_and_app_id on installed_apps installed_apps_1 (cost=0.44..21.17 rows=74 width=4) (actual time=0.016..0.027 rows=13 loops=1)

  • Index Cond: (user_id = 7552144)
  • Heap Fetches: 13
20. 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.44..21.17 rows=74 width=4) (never executed)

  • Index Cond: (user_id = 7552144)
  • Heap Fetches: 0
21. 0.028 9.590 ↓ 0.0 0 14

Bitmap Heap Scan on completed_offers completed_offers_1 (cost=28.81..29.93 rows=1 width=8) (actual time=0.685..0.685 rows=0 loops=14)

  • Recheck Cond: (((device_id = 7221567) OR (user_id = 7552144)) AND (offer_id = offers.id))
  • Filter: ((device_id = 7221567) OR ((user_id = 7552144) AND (device_id IS NULL)))
  • Heap Blocks: exact=1
22. 0.154 9.562 ↓ 0.0 0 14

BitmapAnd (cost=28.81..28.81 rows=1 width=0) (actual time=0.683..0.683 rows=0 loops=14)

23. 0.014 0.140 ↓ 0.0 0 14

BitmapOr (cost=4.30..4.30 rows=158 width=0) (actual time=0.010..0.010 rows=0 loops=14)

24. 0.084 0.084 ↑ 5.3 13 14

Bitmap Index Scan on index_completed_offers_on_device_id (cost=0.00..2.06 rows=69 width=0) (actual time=0.006..0.006 rows=13 loops=14)

  • Index Cond: (device_id = 7221567)
25. 0.042 0.042 ↑ 6.8 13 14

Bitmap Index Scan on index_completed_offers_on_user_id (cost=0.00..2.21 rows=89 width=0) (actual time=0.003..0.003 rows=13 loops=14)

  • Index Cond: (user_id = 7552144)
26. 9.268 9.268 ↓ 1.9 3,771 14

Bitmap Index Scan on index_completed_offers_on_offer_id_and_user_id (cost=0.00..24.26 rows=2,003 width=0) (actual time=0.662..0.662 rows=3,771 loops=14)

  • Index Cond: (offer_id = offers.id)
27. 0.052 0.052 ↑ 1.0 1 13

Index Scan using apps_pkey on apps (cost=0.28..2.50 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=13)

  • Index Cond: (id = selected_offers.app_id)
28. 0.065 0.065 ↑ 2.0 1 13

Index Scan using index_offer_steps_on_offer_id on offer_steps (cost=0.29..3.00 rows=2 width=11) (actual time=0.005..0.005 rows=1 loops=13)

  • Index Cond: (offer_id = selected_offers.id)
Planning time : 2.270 ms
Execution time : 17.606 ms