explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GpOc

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,975.587 2,265.308 ↓ 5.9 37,448 1

Hash Anti Join (cost=2,748.75..16,357.10 rows=6,393 width=304) (actual time=61.348..2,265.308 rows=37,448 loops=1)

  • Hash Cond: ((k.name)::text = (ar.name)::text)
2.          

CTE apps_recomendations

3. 0.058 0.227 ↓ 15.4 108 1

Nested Loop (cost=0.58..86.10 rows=7 width=81) (actual time=0.046..0.227 rows=108 loops=1)

4. 0.025 0.025 ↑ 1.0 1 1

Index Scan using index_apps_on_store_id on apps a (cost=0.29..8.30 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: ((store_id)::text = 'com.love.couple.romantic.stickers.forwhatsapp.wastickerapps'::text)
5. 0.144 0.144 ↓ 3.5 108 1

Index Scan using index_recommendations_on_app_id on recommendations r (cost=0.29..77.48 rows=31 width=81) (actual time=0.016..0.144 rows=108 loops=1)

  • Index Cond: (app_id = a.id)
6. 107.613 289.356 ↓ 5.9 37,556 1

Hash Join (cost=2,662.43..16,174.08 rows=6,394 width=272) (actual time=60.926..289.356 rows=37,556 loops=1)

  • Hash Cond: (ks.keyword_id = k.id)
7. 120.922 120.922 ↓ 5.0 95,606 1

Index Scan using "index_keyword_stats_on_parsed_at_DESC" on keyword_stats ks (cost=0.44..13,376.70 rows=19,054 width=233) (actual time=0.015..120.922 rows=95,606 loops=1)

  • Index Cond: (parsed_at = '2020-03-19'::date)
8. 25.106 60.821 ↓ 1.2 37,862 1

Hash (cost=2,258.49..2,258.49 rows=32,280 width=43) (actual time=60.821..60.821 rows=37,862 loops=1)

  • Buckets: 65,536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3,744kB
9. 25.182 35.715 ↓ 1.2 37,862 1

Bitmap Heap Scan on keywords k (cost=771.29..2,258.49 rows=32,280 width=43) (actual time=10.654..35.715 rows=37,862 loops=1)

  • Recheck Cond: (((platform)::text = 'android'::text) AND ((country)::text = 'ru'::text))
  • Heap Blocks: exact=972
10. 10.533 10.533 ↓ 1.2 37,862 1

Bitmap Index Scan on keywords_platform_country_idx (cost=0.00..763.22 rows=32,280 width=0) (actual time=10.533..10.533 rows=37,862 loops=1)

  • Index Cond: (((platform)::text = 'android'::text) AND ((country)::text = 'ru'::text))
11. 0.029 0.365 ↓ 15.4 108 1

Hash (cost=0.14..0.14 rows=7 width=32) (actual time=0.365..0.365 rows=108 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
12. 0.336 0.336 ↓ 15.4 108 1

CTE Scan on apps_recomendations ar (cost=0.00..0.14 rows=7 width=32) (actual time=0.049..0.336 rows=108 loops=1)

Planning time : 1.231 ms
Execution time : 2,284.855 ms