explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qyIC

Settings
# exclusive inclusive rows x rows loops node
1. 23.585 4,268.112 ↑ 186.4 64 1

HashAggregate (cost=3,093,427.86..3,093,666.44 rows=11,929 width=116) (actual time=4,267.984..4,268.112 rows=64 loops=1)

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

CTE latest_ranking_params

3. 31.704 3,994.846 ↑ 6.0 19,904 1

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

4. 753.440 3,784.006 ↑ 6.0 19,904 1

Nested Loop Left Join (cost=13.96..2,056,285.70 rows=119,294 width=43) (actual time=0.413..3,784.006 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. 33.235 303.718 ↑ 6.0 19,904 1

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

6. 17.163 31.635 ↑ 6.0 19,904 1

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

7. 0.186 2.824 ↑ 4.7 64 1

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

8. 0.418 2.510 ↑ 4.7 64 1

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

9. 0.560 0.560 ↓ 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.021..0.560 rows=383 loops=1)

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

Index Scan using markets_pkey on markets m (cost=0.29..5.67 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
11. 0.105 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.004 0.023 ↑ 1.0 1 1

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

13. 0.011 0.011 ↑ 1.0 1 1

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

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

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

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

16. 0.610 0.645 ↑ 1.3 311 1

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

  • Recheck Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 131
  • Heap Blocks: exact=122
17. 0.035 0.035 ↑ 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.035 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,726.848 2,726.848 ↓ 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.009..0.137 rows=81 loops=19,904)

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

Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.007..0.009 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. 20.625 4,161.812 ↑ 6.0 19,904 1

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

  • Hash Cond: (lrp.market_id = m_1.market_id)
25. 27.907 4,068.509 ↑ 6.0 19,904 1

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

  • Hash Cond: (lrp.keyword_id = k.keyword_id)
26. 4,021.767 4,021.767 ↑ 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.433..4,021.767 rows=19,904 loops=1)

27. 9.855 18.835 ↑ 1.0 18,134 1

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

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

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

29. 37.268 72.678 ↑ 1.0 66,377 1

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

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

31. 4,244.527 4,244.527 ↑ 6.0 19,904 1

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

Planning time : 3.488 ms
Execution time : 4,269.737 ms