explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xI3b : Optimization for: plan #GpOc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,563.049 2,795.082 ↓ 5.9 37,448 1

Hash Anti Join (cost=2,936.25..16,544.60 rows=6,393 width=304) (actual time=42.686..2,795.082 rows=37,448 loops=1)

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

CTE apps_recomendations

3. 0.073 0.202 ↓ 15.4 108 1

Nested Loop (cost=0.58..86.10 rows=7 width=81) (actual time=0.047..0.202 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.104 0.104 ↓ 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.104 rows=108 loops=1)

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

Hash Join (cost=2,849.92..16,361.58 rows=6,394 width=272) (actual time=42.270..231.686 rows=37,556 loops=1)

  • Hash Cond: (ks.keyword_id = k.id)
7. 120.073 120.073 ↓ 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.017..120.073 rows=95,606 loops=1)

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

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

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

Seq Scan on keywords k (cost=0.00..2,445.98 rows=32,280 width=43) (actual time=0.014..26.231 rows=37,862 loops=1)

  • Filter: (((platform)::text = 'android'::text) AND ((country)::text = 'ru'::text))
  • Rows Removed by Filter: 58,337
10. 0.058 0.347 ↓ 15.4 108 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
11. 0.289 0.289 ↓ 15.4 108 1

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

Planning time : 0.791 ms
Execution time : 2,806.591 ms