explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U30q : prod rankings

Settings
# exclusive inclusive rows x rows loops node
1. 29.138 7,331.172 ↑ 186.4 64 1

HashAggregate (cost=55,856,887.19..55,857,125.77 rows=11,929 width=116) (actual time=7,331.044..7,331.172 rows=64 loops=1)

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

CTE latest_ranking_params

3. 49.336 3,670.444 ↑ 6.0 19,904 1

Nested Loop Left Join (cost=14.52..3,082,512.33 rows=119,294 width=69) (actual time=0.803..3,670.444 rows=19,904 loops=1)

4. 760.712 3,461.876 ↑ 6.0 19,904 1

Nested Loop Left Join (cost=13.96..2,056,285.70 rows=119,294 width=43) (actual time=0.770..3,461.876 rows=19,904 loops=1)

  • Join Filter: ((d_1.domain_id = r.domain_id) AND (multisample_rankings.date = r.date) AND (d.name = r.domain))
  • Rows Removed by Join Filter: 1590047
5. 42.366 312.684 ↑ 6.0 19,904 1

Nested Loop Left Join (cost=13.40..1,030,357.30 rows=119,294 width=33) (actual time=0.255..312.684 rows=19,904 loops=1)

6. 17.312 31.470 ↑ 6.0 19,904 1

Nested Loop (cost=12.84..4,130.66 rows=119,294 width=29) (actual time=0.209..31.470 rows=19,904 loops=1)

7. 0.138 2.510 ↑ 4.7 64 1

Nested Loop (cost=1.14..2,436.94 rows=300 width=25) (actual time=0.086..2.510 rows=64 loops=1)

8. 0.593 2.244 ↑ 4.7 64 1

Nested Loop (cost=0.58..2,416.59 rows=300 width=8) (actual time=0.048..2.244 rows=64 loops=1)

9. 0.502 0.502 ↓ 1.1 383 1

Index Scan using domains_markets_pkey on domains_markets dm (cost=0.29..487.19 rows=340 width=8) (actual time=0.029..0.502 rows=383 loops=1)

  • Index Cond: (domain_id = 725)
  • Filter: (NOT deleted)
10. 1.149 1.149 ↓ 0.0 0 383

Index Scan using markets_pkey on markets m (cost=0.29..5.67 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=383)

  • Index Cond: (market_id = dm.market_id)
  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 1
11. 0.093 0.128 ↑ 1.0 1 64

Materialize (cost=0.56..16.61 rows=1 width=21) (actual time=0.001..0.002 rows=1 loops=64)

12. 0.005 0.035 ↑ 1.0 1 1

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

13. 0.017 0.017 ↑ 1.0 1 1

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

  • Index Cond: (domain_id = 725)
14. 0.013 0.013 ↑ 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.010..0.013 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
  • Heap Fetches: 1
15. 10.876 11.648 ↑ 1.3 311 64

Materialize (cost=11.70..202.22 rows=398 width=8) (actual time=0.002..0.182 rows=311 loops=64)

16. 0.692 0.772 ↑ 1.3 311 1

Bitmap Heap Scan on domains_keywords dk (cost=11.70..200.23 rows=398 width=8) (actual time=0.117..0.772 rows=311 loops=1)

  • Recheck Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 131
  • Heap Blocks: exact=122
17. 0.080 0.080 ↑ 1.0 442 1

Bitmap Index Scan on domains_keywords_pkey (cost=0.00..11.60 rows=442 width=0) (actual time=0.079..0.080 rows=442 loops=1)

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

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

19. 199.040 199.040 ↑ 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.010..0.010 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: 0
20. 2,388.480 2,388.480 ↓ 81.0 81 19,904

Index Scan using multisample_rankings_domain_id_market_id_keyword_id_domain__idx on multisample_rankings r (cost=0.56..8.58 rows=1 width=42) (actual time=0.010..0.120 rows=81 loops=19,904)

  • Index Cond: ((domain_id = 725) AND (dm.market_id = market_id) AND (dk.keyword_id = keyword_id))
21. 39.808 159.232 ↑ 1.0 1 19,904

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

22. 119.424 119.424 ↑ 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.006..0.006 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))
23.          

CTE latest_rankings

24. 30.135 7,201.893 ↑ 6.0 19,904 1

Nested Loop Left Join (cost=3,573.77..52,769,603.10 rows=119,294 width=159) (actual time=103.480..7,201.893 rows=19,904 loops=1)

25. 23.069 3,847.790 ↑ 6.0 19,904 1

Hash Join (cost=3,131.50..6,143.77 rows=119,294 width=151) (actual time=100.204..3,847.790 rows=19,904 loops=1)

  • Hash Cond: (lrp.market_id = m_1.market_id)
26. 28.627 3,748.104 ↑ 6.0 19,904 1

Hash Join (cost=716.02..3,415.12 rows=119,294 width=107) (actual time=23.406..3,748.104 rows=19,904 loops=1)

  • Hash Cond: (lrp.keyword_id = k.keyword_id)
27. 3,696.949 3,696.949 ↑ 6.0 19,904 1

CTE Scan on latest_ranking_params lrp (cost=0.00..2,385.88 rows=119,294 width=84) (actual time=0.807..3,696.949 rows=19,904 loops=1)

28. 11.596 22.528 ↑ 1.0 18,134 1

Hash (cost=489.34..489.34 rows=18,134 width=27) (actual time=22.528..22.528 rows=18,134 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1333kB
29. 10.932 10.932 ↑ 1.0 18,134 1

Seq Scan on keywords k (cost=0.00..489.34 rows=18,134 width=27) (actual time=0.019..10.932 rows=18,134 loops=1)

30. 38.718 76.617 ↑ 1.0 66,377 1

Hash (cost=1,585.77..1,585.77 rows=66,377 width=48) (actual time=76.617..76.617 rows=66,377 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6479kB
31. 37.899 37.899 ↑ 1.0 66,377 1

Seq Scan on markets m_1 (cost=0.00..1,585.77 rows=66,377 width=48) (actual time=0.010..37.899 rows=66,377 loops=1)

32. 79.616 3,323.968 ↑ 1.0 1 19,904

Limit (cost=442.27..442.28 rows=1 width=16) (actual time=0.164..0.167 rows=1 loops=19,904)

33. 79.616 3,244.352 ↑ 1.0 1 19,904

Sort (cost=442.27..442.28 rows=1 width=16) (actual time=0.163..0.163 rows=1 loops=19,904)

  • Sort Key: ak.created_at DESC
  • Sort Method: quicksort Memory: 25kB
34. 2,527.808 3,164.736 ↑ 1.0 1 19,904

Bitmap Heap Scan on adwords_keywords ak (cost=5.48..442.26 rows=1 width=16) (actual time=0.116..0.159 rows=1 loops=19,904)

  • Recheck Cond: (lrp.market_id = market_id)
  • Filter: (k.name = name)
  • Rows Removed by Filter: 822
  • Heap Blocks: exact=650301
35. 636.928 636.928 ↓ 5.8 823 19,904

Bitmap Index Scan on idx_adwords_keywords_market (cost=0.00..5.48 rows=141 width=0) (actual time=0.032..0.032 rows=823 loops=19,904)

  • Index Cond: (lrp.market_id = market_id)
36. 7,302.034 7,302.034 ↑ 6.0 19,904 1

CTE Scan on latest_rankings (cost=0.00..2,982.35 rows=119,294 width=108) (actual time=103.500..7,302.034 rows=19,904 loops=1)

Planning time : 6.713 ms
Execution time : 7,333.095 ms