explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nmjN

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 8.792 3,870.105 ↑ 209.2 64 1

GroupAggregate (cost=65,886,236.35..65,888,847.08 rows=13,388 width=116) (actual time=3,859.466..3,870.105 rows=64 loops=1)

  • Group Key: latest_rankings.market_id, latest_rankings.market, ((latest_rankings.coordinate)::text)
2.          

CTE latest_ranking_params

3. 19.859 1,394.164 ↑ 6.7 19,904 1

Nested Loop Left Join (cost=771,743.88..3,092,719.29 rows=133,884 width=69) (actual time=970.532..1,394.164 rows=19,904 loops=1)

4. 127.355 1,234.977 ↑ 6.7 19,904 1

Hash Left Join (cost=771,743.32..1,940,982.18 rows=133,884 width=43) (actual time=970.499..1,234.977 rows=19,904 loops=1)

  • Hash Cond: ((d_1.domain_id = r.domain_id) AND (dk.keyword_id = r.keyword_id) AND (dm.market_id = r.market_id) AND (multisample_rankings.date = r.date) AND (d.name = r.domain))
5. 0.482 138.045 ↑ 6.7 19,904 1

Nested Loop Left Join (cost=374.37..1,156,093.00 rows=133,884 width=33) (actual time=0.248..138.045 rows=19,904 loops=1)

6. 3.425 18.139 ↑ 6.7 19,904 1

Nested Loop (cost=373.81..4,355.89 rows=133,884 width=29) (actual time=0.214..18.139 rows=19,904 loops=1)

7. 0.719 0.719 ↑ 1.3 311 1

Index Scan using domains_keywords_pkey on domains_keywords dk (cost=0.29..633.94 rows=397 width=8) (actual time=0.018..0.719 rows=311 loops=1)

  • Index Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 130
8. 1.498 13.995 ↑ 5.3 64 311

Materialize (cost=373.52..2,050.43 rows=337 width=25) (actual time=0.001..0.045 rows=64 loops=311)

9. 0.013 12.497 ↑ 5.3 64 1

Nested Loop (cost=373.52..2,048.74 rows=337 width=25) (actual time=0.195..12.497 rows=64 loops=1)

10. 0.002 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.56..16.60 rows=1 width=21) (actual time=0.012..0.015 rows=1 loops=1)

11. 0.009 0.009 ↑ 1.0 1 1

Index Scan using domains_pkey on domains d (cost=0.28..8.29 rows=1 width=21) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
12. 0.004 0.004 ↑ 1.0 1 1

Index Only Scan using domains_pkey on domains d_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
  • Heap Fetches: 1
13. 4.546 12.469 ↑ 5.3 64 1

Hash Join (cost=372.97..2,028.77 rows=337 width=8) (actual time=0.181..12.469 rows=64 loops=1)

  • Hash Cond: (m.market_id = dm.market_id)
14. 7.774 7.774 ↑ 1.0 58,465 1

Seq Scan on markets m (cost=0.00..1,501.99 rows=58,591 width=4) (actual time=0.004..7.774 rows=58,465 loops=1)

  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 7034
15. 0.043 0.149 ↓ 1.0 383 1

Hash (cost=368.26..368.26 rows=377 width=8) (actual time=0.149..0.149 rows=383 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
16. 0.085 0.106 ↓ 1.0 383 1

Bitmap Heap Scan on domains_markets dm (cost=11.26..368.26 rows=377 width=8) (actual time=0.028..0.106 rows=383 loops=1)

  • Recheck Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Heap Blocks: exact=6
17. 0.021 0.021 ↑ 1.0 383 1

Bitmap Index Scan on domains_markets_pkey (cost=0.00..11.16 rows=383 width=0) (actual time=0.021..0.021 rows=383 loops=1)

  • Index Cond: (domain_id = 725)
18. 19.904 119.424 ↑ 1.0 1 19,904

Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=19,904)

19. 99.520 99.520 ↑ 1.0 1 19,904

Index Only Scan Backward using multisample_rankings_pkey on multisample_rankings (cost=0.56..8.58 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=19,904)

  • Index Cond: ((domain_id = d_1.domain_id) AND (keyword_id = dk.keyword_id) AND (market_id = dm.market_id))
  • Heap Fetches: 13030
20. 369.658 969.577 ↓ 1.0 1,135,527 1

Hash (cost=736,942.37..736,942.37 rows=1,100,248 width=41) (actual time=969.577..969.577 rows=1,135,527 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 3144kB
21. 599.919 599.919 ↓ 1.0 1,135,527 1

Index Scan using idx_multisample_rankings_domain_id on multisample_rankings r (cost=0.43..736,942.37 rows=1,100,248 width=41) (actual time=0.014..599.919 rows=1,135,527 loops=1)

  • Index Cond: (domain_id = 725)
22. 0.000 139.328 ↑ 1.0 1 19,904

Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=19,904)

23. 139.328 139.328 ↑ 1.0 1 19,904

Index Scan using multisample_rankings_domain_id_market_id_keyword_id_domain__idx on multisample_rankings r_1 (cost=0.56..8.58 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=19,904)

  • Index Cond: ((d_1.domain_id = domain_id) AND (dm.market_id = market_id) AND (dk.keyword_id = keyword_id))
24.          

CTE latest_rankings

25. 16.402 3,782.812 ↑ 6.7 19,904 1

Nested Loop Left Join (cost=3,908.35..62,770,988.82 rows=133,884 width=159) (actual time=998.237..3,782.812 rows=19,904 loops=1)

26. 9.018 1,457.546 ↑ 6.7 19,904 1

Hash Join (cost=3,439.61..11,058.29 rows=133,884 width=151) (actual time=997.986..1,457.546 rows=19,904 loops=1)

  • Hash Cond: (lrp.keyword_id = k.keyword_id)
27. 18.755 1,444.036 ↑ 6.7 19,904 1

Hash Join (cost=2,896.73..10,163.88 rows=133,884 width=128) (actual time=993.482..1,444.036 rows=19,904 loops=1)

  • Hash Cond: (lrp.market_id = m_1.market_id)
28. 1,402.362 1,402.362 ↑ 6.7 19,904 1

CTE Scan on latest_ranking_params lrp (cost=0.00..2,677.68 rows=133,884 width=84) (actual time=970.534..1,402.362 rows=19,904 loops=1)

29. 12.174 22.919 ↑ 1.0 65,499 1

Hash (cost=1,501.99..1,501.99 rows=65,499 width=48) (actual time=22.918..22.919 rows=65,499 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3211kB
30. 10.745 10.745 ↑ 1.0 65,499 1

Seq Scan on markets m_1 (cost=0.00..1,501.99 rows=65,499 width=48) (actual time=0.007..10.745 rows=65,499 loops=1)

31. 2.295 4.492 ↑ 1.0 17,106 1

Hash (cost=329.06..329.06 rows=17,106 width=27) (actual time=4.492..4.492 rows=17,106 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1272kB
32. 2.197 2.197 ↑ 1.0 17,106 1

Seq Scan on keywords k (cost=0.00..329.06 rows=17,106 width=27) (actual time=0.009..2.197 rows=17,106 loops=1)

33. 0.000 2,308.864 ↑ 1.0 1 19,904

Limit (cost=468.74..468.74 rows=1 width=16) (actual time=0.116..0.116 rows=1 loops=19,904)

34. 39.808 2,308.864 ↑ 1.0 1 19,904

Sort (cost=468.74..468.74 rows=1 width=16) (actual time=0.116..0.116 rows=1 loops=19,904)

  • Sort Key: ak.created_at DESC
  • Sort Method: quicksort Memory: 25kB
35. 1,771.456 2,269.056 ↑ 1.0 1 19,904

Bitmap Heap Scan on adwords_keywords ak (cost=5.56..468.73 rows=1 width=16) (actual time=0.085..0.114 rows=1 loops=19,904)

  • Recheck Cond: (lrp.market_id = market_id)
  • Filter: (k.name = name)
  • Rows Removed by Filter: 812
  • Heap Blocks: exact=584680
36. 497.600 497.600 ↓ 5.3 813 19,904

Bitmap Index Scan on idx_adwords_keywords_market (cost=0.00..5.56 rows=152 width=0) (actual time=0.025..0.025 rows=813 loops=19,904)

  • Index Cond: (lrp.market_id = market_id)
37. 11.150 3,861.313 ↑ 6.7 19,904 1

Sort (cost=22,528.24..22,862.95 rows=133,884 width=108) (actual time=3,859.320..3,861.313 rows=19,904 loops=1)

  • Sort Key: latest_rankings.market_id, latest_rankings.market, ((latest_rankings.coordinate)::text)
  • Sort Method: quicksort Memory: 3385kB
38. 3,850.163 3,850.163 ↑ 6.7 19,904 1

CTE Scan on latest_rankings (cost=0.00..3,347.10 rows=133,884 width=108) (actual time=998.248..3,850.163 rows=19,904 loops=1)

Planning time : 2.580 ms
Execution time : 3,871.513 ms