explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B0Fd

Settings
# exclusive inclusive rows x rows loops node
1. 25.240 5,147.366 ↑ 202.4 64 1

HashAggregate (cost=3,358,395.49..3,358,654.55 rows=12,953 width=116) (actual time=5,147.238..5,147.366 rows=64 loops=1)

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

CTE latest_ranking_params

3. 39.902 4,853.113 ↑ 6.5 19,904 1

Nested Loop Left Join (cost=14.52..3,346,812.32 rows=129,526 width=69) (actual time=0.426..4,853.113 rows=19,904 loops=1)

4. 767.531 4,634.075 ↑ 6.5 19,904 1

Nested Loop Left Join (cost=13.96..2,232,564.90 rows=129,526 width=43) (actual time=0.408..4,634.075 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. 32.891 303.728 ↑ 6.5 19,904 1

Nested Loop Left Join (cost=13.40..1,118,641.30 rows=129,526 width=33) (actual time=0.147..303.728 rows=19,904 loops=1)

6. 17.719 31.989 ↑ 6.5 19,904 1

Nested Loop (cost=12.84..4,393.89 rows=129,526 width=29) (actual time=0.107..31.989 rows=19,904 loops=1)

7. 0.655 2.302 ↑ 5.1 64 1

Nested Loop (cost=0.58..2,555.21 rows=325 width=8) (actual time=0.029..2.302 rows=64 loops=1)

8. 0.498 0.498 ↓ 1.0 383 1

Index Scan using domains_markets_pkey on domains_markets dm (cost=0.29..512.20 rows=371 width=8) (actual time=0.019..0.498 rows=383 loops=1)

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

Index Scan using markets_pkey on markets m (cost=0.29..5.51 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
10. 11.003 11.968 ↑ 1.3 311 64

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

11. 0.262 0.965 ↑ 1.3 311 1

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

12. 0.002 0.023 ↑ 1.0 1 1

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

13. 0.012 0.012 ↑ 1.0 1 1

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

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

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

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

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

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

  • Index Cond: (domain_id = 725)
17. 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)

18. 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
19. 3,562.816 3,562.816 ↓ 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.009..0.179 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 179.136 ↑ 1.0 1 19,904

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

21. 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.006..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))
22.          

CTE latest_rankings

23. 21.765 5,034.302 ↑ 6.5 19,904 1

Hash Join (cost=3,131.50..6,402.13 rows=129,526 width=151) (actual time=104.171..5,034.302 rows=19,904 loops=1)

  • Hash Cond: (lrp.market_id = m_1.market_id)
24. 28.198 4,929.728 ↑ 6.5 19,904 1

Hash Join (cost=716.02..3,646.62 rows=129,526 width=107) (actual time=20.989..4,929.728 rows=19,904 loops=1)

  • Hash Cond: (lrp.keyword_id = k.keyword_id)
25. 4,881.026 4,881.026 ↑ 6.5 19,904 1

CTE Scan on latest_ranking_params lrp (cost=0.00..2,590.52 rows=129,526 width=84) (actual time=0.428..4,881.026 rows=19,904 loops=1)

26. 10.536 20.504 ↑ 1.0 18,134 1

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

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

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

28. 42.554 82.809 ↑ 1.0 66,377 1

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

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

30. 5,122.126 5,122.126 ↑ 6.5 19,904 1

CTE Scan on latest_rankings (cost=0.00..3,238.15 rows=129,526 width=108) (actual time=104.188..5,122.126 rows=19,904 loops=1)

Planning time : 6.653 ms
Execution time : 5,149.022 ms