explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A95m : Optimization for: plan #nmjN

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 14.178 1,618.230 ↑ 198.4 64 1

HashAggregate (cost=4,101,587.13..4,101,841.13 rows=12,700 width=116) (actual time=1,618.153..1,618.230 rows=64 loops=1)

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

CTE latest_ranking_params

3. 12.324 1,547.861 ↑ 6.4 19,904 1

Nested Loop Left Join (cost=792,207.51..4,096,507.33 rows=126,995 width=144) (actual time=954.588..1,547.861 rows=19,904 loops=1)

4. 0.000 1,356.401 ↑ 6.4 19,904 1

Nested Loop Left Join (cost=792,207.09..2,998,000.58 rows=126,995 width=110) (actual time=954.559..1,356.401 rows=19,904 loops=1)

5. 128.096 1,217.090 ↑ 6.4 19,904 1

Hash Left Join (cost=792,206.53..1,905,526.10 rows=126,995 width=110) (actual time=954.536..1,217.090 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))
6. 7.499 137.071 ↑ 6.4 19,904 1

Nested Loop Left Join (cost=161.15..1,097,869.91 rows=126,995 width=100) (actual time=2.045..137.071 rows=19,904 loops=1)

7. 3.686 10.148 ↑ 6.4 19,904 1

Nested Loop (cost=160.59..5,395.43 rows=126,995 width=96) (actual time=2.031..10.148 rows=19,904 loops=1)

8. 0.083 1.278 ↑ 5.0 64 1

Nested Loop (cost=0.87..3,252.72 rows=320 width=52) (actual time=0.030..1.278 rows=64 loops=1)

  • Join Filter: (dm.market_id = m.market_id)
9. 0.174 1.131 ↑ 5.0 64 1

Nested Loop (cost=0.58..2,446.81 rows=320 width=12) (actual time=0.027..1.131 rows=64 loops=1)

10. 0.191 0.191 ↓ 1.1 383 1

Index Scan using domains_markets_pkey on domains_markets dm (cost=0.29..460.72 rows=358 width=8) (actual time=0.019..0.191 rows=383 loops=1)

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

Index Scan using markets_pkey on markets m_1 (cost=0.29..5.55 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=383)

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

Index Scan using markets_pkey on markets m (cost=0.29..2.51 rows=1 width=48) (actual time=0.001..0.001 rows=1 loops=64)

  • Index Cond: (market_id = m_1.market_id)
13. 1.467 5.184 ↑ 1.3 311 64

Materialize (cost=159.72..555.70 rows=397 width=48) (actual time=0.031..0.081 rows=311 loops=64)

14. 0.058 3.717 ↑ 1.3 311 1

Nested Loop (cost=159.72..553.71 rows=397 width=48) (actual time=1.999..3.717 rows=311 loops=1)

15. 0.001 0.016 ↑ 1.0 1 1

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

16. 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.012..0.012 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
17. 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
18. 1.570 3.643 ↑ 1.3 311 1

Hash Join (cost=159.17..533.14 rows=397 width=31) (actual time=1.984..3.643 rows=311 loops=1)

  • Hash Cond: (k.keyword_id = dk.keyword_id)
19. 1.522 1.522 ↑ 1.0 17,106 1

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

20. 0.042 0.551 ↑ 1.3 311 1

Hash (cost=154.21..154.21 rows=397 width=8) (actual time=0.551..0.551 rows=311 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
21. 0.480 0.509 ↑ 1.3 311 1

Bitmap Heap Scan on domains_keywords dk (cost=11.69..154.21 rows=397 width=8) (actual time=0.043..0.509 rows=311 loops=1)

  • Recheck Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 130
  • Heap Blocks: exact=114
22. 0.029 0.029 ↑ 1.0 441 1

Bitmap Index Scan on domains_keywords_pkey (cost=0.00..11.59 rows=441 width=0) (actual time=0.029..0.029 rows=441 loops=1)

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

24. 119.424 119.424 ↑ 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.006..0.006 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
25. 362.589 951.923 ↓ 1.0 1,135,527 1

Hash (cost=756,538.15..756,538.15 rows=1,134,810 width=41) (actual time=951.923..951.923 rows=1,135,527 loops=1)

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

Index Scan using idx_multisample_rankings_domain_id on multisample_rankings r (cost=0.43..756,538.15 rows=1,134,810 width=41) (actual time=0.016..589.334 rows=1,135,527 loops=1)

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

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

28. 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))
29. 19.904 179.136 ↑ 1.0 1 19,904

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

30. 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.63 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=19,904)

  • Index Cond: ((k.name = name) AND (dm.market_id = market_id))
31. 1,604.052 1,604.052 ↑ 6.4 19,904 1

CTE Scan on latest_ranking_params (cost=0.00..3,174.88 rows=126,995 width=108) (actual time=954.597..1,604.052 rows=19,904 loops=1)

Planning time : 3.452 ms
Execution time : 1,618.781 ms