explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QtZ9 : before target geo opt with index

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.414 2.779 ↓ 67.0 201 1

Sort (cost=1,447.87..1,447.87 rows=3 width=5,195) (actual time=2.753..2.779 rows=201 loops=1)

  • Sort Key: selected_offers.previous_offer_id, apps.vip DESC, selected_offers.number, offer_steps.order_index
  • Sort Method: quicksort Memory: 229kB
2. 0.250 2.365 ↓ 67.0 201 1

Nested Loop Left Join (cost=1,432.65..1,447.84 rows=3 width=5,195) (actual time=0.326..2.365 rows=201 loops=1)

3. 0.216 1.914 ↓ 201.0 201 1

Nested Loop Left Join (cost=1,428.47..1,436.53 rows=1 width=5,163) (actual time=0.322..1.914 rows=201 loops=1)

4. 1.497 1.497 ↓ 201.0 201 1

CTE Scan on selected_offers (cost=1,428.33..1,428.35 rows=1 width=5,227) (actual time=0.316..1.497 rows=201 loops=1)

5.          

CTE selected_offers

6. 0.119 1.054 ↓ 201.0 201 1

Nested Loop Left Join (cost=82.20..1,428.33 rows=1 width=2,522) (actual time=0.309..1.054 rows=201 loops=1)

  • Join Filter: (completed_offers_2.offer_id = offers.id)
  • 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. 0.767 0.935 ↓ 5.3 201 1

Seq Scan on offers (cost=82.20..1,415.46 rows=38 width=2,522) (actual time=0.303..0.935 rows=201 loops=1)

  • Filter: ((NOT hidden) AND (NOT archived) AND iphone AND (target_gender IS NULL) AND ((started_at <= '2020-06-29 07:02:14.601493'::timestamp without time zone) OR (started_at IS NULL)) AND (rem_day_installs > 0) 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 ((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: 100
8.          

SubPlan (for Seq Scan)

9. 0.000 0.003 ↓ 0.0 0 1

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

  • Recheck Cond: (user_id = 1)
10. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Index Cond: (user_id = 1)
11. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on completed_offers (cost=0.00..12.10 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((rewarded AND (device_id = 1)) OR ((user_id = 1) AND (device_id IS NULL)))
12. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on completed_offers completed_offers_1 (cost=0.00..12.10 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((rewarded AND (device_id = 1)) OR ((user_id = 1) AND (device_id IS NULL)))
13. 0.000 0.000 ↓ 0.0 0 201

Materialize (cost=4.18..12.66 rows=4 width=4) (actual time=0.000..0.000 rows=0 loops=201)

14. 0.000 0.001 ↓ 0.0 0 1

Bitmap Heap Scan on installed_apps (cost=4.18..12.64 rows=4 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Recheck Cond: (device_id = 1)
15. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on index_installed_apps_on_device_id_and_app_id (cost=0.00..4.18 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (device_id = 1)
16. 0.117 0.163 ↓ 150.5 301 1

Hash Left Join (cost=12.69..29.87 rows=2 width=4) (actual time=0.016..0.163 rows=301 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)))
17. 0.044 0.044 ↑ 1.0 301 1

Seq Scan on apps apps_1 (cost=0.00..16.01 rows=301 width=5) (actual time=0.003..0.044 rows=301 loops=1)

18. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=12.64..12.64 rows=4 width=4) (actual time=0.002..0.002 rows=0 loops=1)

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

Bitmap Heap Scan on installed_apps installed_apps_1 (cost=4.18..12.64 rows=4 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (user_id = 1)
20. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on index_installed_apps_on_user_id_and_app_id (cost=0.00..4.18 rows=4 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (user_id = 1)
21. 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 = 1)
22. 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 = 1)
23. 0.000 0.000 ↓ 0.0 0 201

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

24. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on completed_offers completed_offers_2 (cost=0.00..12.10 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((device_id = 1) OR ((user_id = 1) AND (device_id IS NULL)))
25. 0.201 0.201 ↑ 1.0 1 201

Index Scan using apps_pkey on apps (cost=0.15..8.17 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=201)

  • Index Cond: (id = selected_offers.app_id)
26. 0.000 0.201 ↓ 0.0 0 201

Bitmap Heap Scan on offer_steps (cost=4.17..11.28 rows=3 width=36) (actual time=0.001..0.001 rows=0 loops=201)

  • Recheck Cond: (offer_id = selected_offers.id)
27. 0.201 0.201 ↓ 0.0 0 201

Bitmap Index Scan on index_offer_steps_on_offer_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=201)

  • Index Cond: (offer_id = selected_offers.id)
Planning time : 2.089 ms
Execution time : 3.117 ms