explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Plqh : Optimization for: Optimization for: plan #nmjN; plan #hM2Z

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13.335 1,472.996 ↑ 195.4 64 1

HashAggregate (cost=2,965,425.96..2,965,676.02 rows=12,503 width=116) (actual time=1,472.919..1,472.996 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. 18.273 1,409.372 ↑ 6.3 19,904 1

Nested Loop Left Join (cost=789,320.09..2,960,424.80 rows=125,029 width=109) (actual time=1,010.628..1,409.372 rows=19,904 loops=1)

4. 127.179 1,271.675 ↑ 6.3 19,904 1

Hash Left Join (cost=789,319.53..1,884,862.83 rows=125,029 width=87) (actual time=1,010.601..1,271.675 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. 8.699 134.768 ↑ 6.3 19,904 1

Nested Loop Left Join (cost=13.68..1,080,516.98 rows=125,029 width=77) (actual time=0.117..134.768 rows=19,904 loops=1)

6. 3.200 6.645 ↑ 6.3 19,904 1

Nested Loop (cost=13.12..4,955.00 rows=125,029 width=73) (actual time=0.083..6.645 rows=19,904 loops=1)

7. 0.092 1.397 ↑ 4.9 64 1

Nested Loop (cost=1.43..3,236.62 rows=315 width=69) (actual time=0.043..1.397 rows=64 loops=1)

  • Join Filter: (dm.market_id = m.market_id)
8. 0.020 1.241 ↑ 4.9 64 1

Nested Loop (cost=1.14..2,443.29 rows=315 width=29) (actual time=0.041..1.241 rows=64 loops=1)

9. 0.192 1.157 ↑ 4.9 64 1

Nested Loop (cost=0.58..2,422.75 rows=315 width=12) (actual time=0.028..1.157 rows=64 loops=1)

10. 0.199 0.199 ↓ 1.1 383 1

Index Scan using domains_markets_pkey on domains_markets dm (cost=0.29..454.20 rows=353 width=8) (actual time=0.020..0.199 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.58 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.050 0.064 ↑ 1.0 1 64

Materialize (cost=0.56..16.61 rows=1 width=21) (actual time=0.000..0.001 rows=1 loops=64)

13. 0.001 0.014 ↑ 1.0 1 1

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

14. 0.010 0.010 ↑ 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.010 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
15. 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
16. 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)
17. 1.504 2.048 ↑ 1.3 311 64

Materialize (cost=11.69..156.19 rows=397 width=8) (actual time=0.001..0.032 rows=311 loops=64)

18. 0.518 0.544 ↑ 1.3 311 1

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

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

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

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

21. 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
22. 385.665 1,009.728 ↑ 1.0 1,135,527 1

Hash (cost=752,912.00..752,912.00 rows=1,163,149 width=41) (actual time=1,009.728..1,009.728 rows=1,135,527 loops=1)

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

Index Scan using idx_multisample_rankings_domain_id on multisample_rankings r (cost=0.43..752,912.00 rows=1,163,149 width=41) (actual time=0.016..624.063 rows=1,135,527 loops=1)

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

25. 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))
26. 1,459.661 1,459.661 ↑ 6.3 19,904 1

CTE Scan on latest_ranking_params (cost=0.00..3,125.72 rows=125,029 width=108) (actual time=1,010.637..1,459.661 rows=19,904 loops=1)

Planning time : 4.247 ms
Execution time : 1,473.449 ms