explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hM2Z : Optimization for: plan #nmjN

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.337 1,583.261 ↑ 206.8 64 1

GroupAggregate (cost=4,235,820.72..4,238,401.88 rows=13,237 width=116) (actual time=1,574.834..1,583.261 rows=64 loops=1)

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

CTE latest_ranking_params

3. 16.457 1,284.475 ↑ 6.7 19,904 1

Nested Loop Left Join (cost=761,136.66..3,056,281.75 rows=132,367 width=69) (actual time=900.971..1,284.475 rows=19,904 loops=1)

4. 118.270 1,148.594 ↑ 6.7 19,904 1

Hash Left Join (cost=761,136.10..1,917,594.63 rows=132,367 width=43) (actual time=900.948..1,148.594 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. 13.586 130.276 ↑ 6.7 19,904 1

Nested Loop Left Join (cost=374.10..1,143,022.32 rows=132,367 width=33) (actual time=0.257..130.276 rows=19,904 loops=1)

6. 3.180 17.170 ↑ 6.7 19,904 1

Nested Loop (cost=373.54..4,335.20 rows=132,367 width=29) (actual time=0.217..17.170 rows=19,904 loops=1)

7. 0.617 0.617 ↑ 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.020..0.617 rows=311 loops=1)

  • Index Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 130
8. 1.514 13.373 ↑ 5.2 64 311

Materialize (cost=373.26..2,049.58 rows=333 width=25) (actual time=0.001..0.043 rows=64 loops=311)

9. 0.012 11.859 ↑ 5.2 64 1

Nested Loop (cost=373.26..2,047.91 rows=333 width=25) (actual time=0.195..11.859 rows=64 loops=1)

10. 0.002 0.018 ↑ 1.0 1 1

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

11. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: (domain_id = 725)
12. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
  • Heap Fetches: 1
13. 4.423 11.829 ↑ 5.2 64 1

Hash Join (cost=372.70..2,027.98 rows=333 width=8) (actual time=0.178..11.829 rows=64 loops=1)

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

Seq Scan on markets m (cost=0.00..1,501.99 rows=58,392 width=4) (actual time=0.005..7.261 rows=58,465 loops=1)

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

Hash (cost=368.03..368.03 rows=374 width=8) (actual time=0.144..0.145 rows=383 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
16. 0.083 0.104 ↓ 1.0 383 1

Bitmap Heap Scan on domains_markets dm (cost=11.23..368.03 rows=374 width=8) (actual time=0.028..0.104 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.14 rows=380 width=0) (actual time=0.021..0.021 rows=383 loops=1)

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

Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.005..0.005 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. 337.252 900.048 ↑ 1.0 1,135,527 1

Hash (cost=725,152.26..725,152.26 rows=1,138,077 width=41) (actual time=900.048..900.048 rows=1,135,527 loops=1)

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

Index Scan using idx_multisample_rankings_domain_id on multisample_rankings r (cost=0.43..725,152.26 rows=1,138,077 width=41) (actual time=0.016..562.796 rows=1,135,527 loops=1)

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

23. 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))
24.          

CTE latest_rankings

25. 14.813 1,513.246 ↑ 6.7 19,904 1

Nested Loop Left Join (cost=3,440.03..1,157,276.21 rows=132,367 width=159) (actual time=927.173..1,513.246 rows=19,904 loops=1)

26. 7.403 1,339.201 ↑ 6.7 19,904 1

Hash Join (cost=3,439.61..10,977.99 rows=132,367 width=151) (actual time=927.140..1,339.201 rows=19,904 loops=1)

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

Hash Join (cost=2,896.73..10,087.55 rows=132,367 width=128) (actual time=922.444..1,327.115 rows=19,904 loops=1)

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

CTE Scan on latest_ranking_params lrp (cost=0.00..2,647.34 rows=132,367 width=84) (actual time=900.973..1,291.703 rows=19,904 loops=1)

29. 11.145 21.444 ↑ 1.0 65,499 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3211kB
30. 10.299 10.299 ↑ 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.006..10.299 rows=65,499 loops=1)

31. 2.387 4.683 ↑ 1.0 17,106 1

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

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

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

33. 0.000 159.232 ↑ 1.0 1 19,904

Limit (cost=0.42..8.64 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=19,904)

34. 159.232 159.232 ↑ 1.0 1 19,904

Index Scan Backward using adwords_keywords_name_created_at_market_id_idx on adwords_keywords ak (cost=0.42..8.64 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=19,904)

  • Index Cond: ((k.name = name) AND (lrp.market_id = market_id))
35. 8.913 1,575.924 ↑ 6.7 19,904 1

Sort (cost=22,262.76..22,593.67 rows=132,367 width=108) (actual time=1,574.697..1,575.924 rows=19,904 loops=1)

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

CTE Scan on latest_rankings (cost=0.00..3,309.18 rows=132,367 width=108) (actual time=927.183..1,567.011 rows=19,904 loops=1)

Planning time : 30.878 ms
Execution time : 1,584.331 ms