explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IbEaP : rankings

Settings
# exclusive inclusive rows x rows loops node
1. 36.613 10,752.483 ↑ 220.1 64 1

HashAggregate (cost=66,365,935.06..66,366,216.84 rows=14,089 width=116) (actual time=10,752.352..10,752.483 rows=64 loops=1)

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

CTE latest_ranking_params

3. 47.459 6,916.716 ↑ 7.1 19,904 1

Nested Loop Left Join (cost=14.52..3,640,428.43 rows=140,893 width=69) (actual time=0.588..6,916.716 rows=19,904 loops=1)

4. 791.832 6,670.217 ↑ 7.1 19,904 1

Nested Loop Left Join (cost=13.96..2,428,396.40 rows=140,893 width=43) (actual time=0.563..6,670.217 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. 30.696 345.073 ↑ 7.1 19,904 1

Nested Loop Left Join (cost=13.40..1,216,716.60 rows=140,893 width=33) (actual time=0.203..345.073 rows=19,904 loops=1)

6. 19.529 35.721 ↑ 7.1 19,904 1

Nested Loop (cost=12.84..4,684.57 rows=140,893 width=29) (actual time=0.156..35.721 rows=19,904 loops=1)

7. 0.382 2.432 ↑ 5.5 64 1

Nested Loop (cost=0.58..2,701.61 rows=354 width=8) (actual time=0.038..2.432 rows=64 loops=1)

8. 0.518 0.518 ↑ 1.0 383 1

Index Scan using domains_markets_pkey on domains_markets dm (cost=0.29..540.99 rows=402 width=8) (actual time=0.024..0.518 rows=383 loops=1)

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

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

  • Index Cond: (market_id = dm.market_id)
  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 1
10. 12.640 13.760 ↑ 1.3 311 64

Materialize (cost=12.26..222.80 rows=398 width=25) (actual time=0.002..0.215 rows=311 loops=64)

11. 0.308 1.120 ↑ 1.3 311 1

Nested Loop (cost=12.26..220.81 rows=398 width=25) (actual time=0.113..1.120 rows=311 loops=1)

12. 0.002 0.044 ↑ 1.0 1 1

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

13. 0.016 0.016 ↑ 1.0 1 1

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

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

  • Index Cond: (domain_id = 725)
  • Heap Fetches: 1
15. 0.719 0.768 ↑ 1.3 311 1

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

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

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

  • Index Cond: (domain_id = 725)
17. 59.712 278.656 ↑ 1.0 1 19,904

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

18. 218.944 218.944 ↑ 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.011..0.011 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
19. 5,533.312 5,533.312 ↓ 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=41) (actual time=0.010..0.278 rows=81 loops=19,904)

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

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

21. 159.232 159.232 ↑ 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.008 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))
22.          

CTE latest_rankings

23. 45.662 10,597.568 ↑ 7.1 19,904 1

Nested Loop Left Join (cost=3,576.58..62,719,870.91 rows=140,893 width=159) (actual time=101.761..10,597.568 rows=19,904 loops=1)

24. 26.223 7,108.514 ↑ 7.1 19,904 1

Hash Join (cost=3,131.50..6,689.16 rows=140,893 width=151) (actual time=100.769..7,108.514 rows=19,904 loops=1)

  • Hash Cond: (lrp.market_id = m_1.market_id)
25. 33.222 7,003.690 ↑ 7.1 19,904 1

Hash Join (cost=716.02..3,903.81 rows=140,893 width=107) (actual time=21.934..7,003.690 rows=19,904 loops=1)

  • Hash Cond: (lrp.keyword_id = k.keyword_id)
26. 6,949.209 6,949.209 ↑ 7.1 19,904 1

CTE Scan on latest_ranking_params lrp (cost=0.00..2,817.86 rows=140,893 width=84) (actual time=0.591..6,949.209 rows=19,904 loops=1)

27. 10.860 21.259 ↑ 1.0 18,134 1

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

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

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

29. 40.259 78.601 ↑ 1.0 66,377 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 6479kB
30. 38.342 38.342 ↑ 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.012..38.342 rows=66,377 loops=1)

31. 79.616 3,443.392 ↑ 1.0 1 19,904

Limit (cost=445.09..445.09 rows=1 width=16) (actual time=0.170..0.173 rows=1 loops=19,904)

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

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

  • Sort Key: ak.created_at DESC
  • Sort Method: quicksort Memory: 25kB
33. 2,607.424 3,284.160 ↑ 1.0 1 19,904

Bitmap Heap Scan on adwords_keywords ak (cost=5.49..445.08 rows=1 width=16) (actual time=0.120..0.165 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
34. 676.736 676.736 ↓ 5.8 823 19,904

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

  • Index Cond: (lrp.market_id = market_id)
35. 10,715.870 10,715.870 ↑ 7.1 19,904 1

CTE Scan on latest_rankings (cost=0.00..3,522.33 rows=140,893 width=108) (actual time=101.781..10,715.870 rows=19,904 loops=1)

Planning time : 7.876 ms
Execution time : 10,754.385 ms