explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mgtk : Optimization for: aggregate by week; plan #C5kb

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.103 6,258.891 ↑ 1.0 3 1

Sort (cost=506,538.27..506,538.28 rows=3 width=84) (actual time=6,258.889..6,258.891 rows=3 loops=1)

  • Sort Key: (to_date((((date_part('isoyear'::text, (rankings.date)::timestamp without time zone)))::text || ((date_part('week'::text, (rankings.date)::timestamp without time zone)))::text), 'iyyyiw'::text))
  • Sort Method: quicksort Memory: 578kB
2.          

CTE all_collected

3. 976.230 4,927.312 ↑ 30.7 40,560 1

Unique (cost=451,643.33..473,961.08 rows=1,245,322 width=16) (actual time=2,970.505..4,927.312 rows=40,560 loops=1)

4. 2,521.536 3,951.082 ↑ 1.0 2,206,459 1

Sort (cost=451,643.33..457,222.77 rows=2,231,775 width=16) (actual time=2,970.502..3,951.082 rows=2,206,459 loops=1)

  • Sort Key: mr.market_id, mr.keyword_id, mr.date
  • Sort Method: external merge Disk: 56152kB
5. 1,429.546 1,429.546 ↑ 1.0 2,206,459 1

Index Only Scan using multisample_rankings_domain_id_market_id_keyword_id_domain__idx on multisample_rankings mr (cost=0.56..178,162.33 rows=2,231,775 width=16) (actual time=0.053..1,429.546 rows=2,206,459 loops=1)

  • Index Cond: (domain_id = 725)
  • Heap Fetches: 0
6.          

CTE collected

7. 65.641 5,423.767 ↓ 10,978.0 32,934 1

Nested Loop (cost=643.30..32,525.24 rows=3 width=33) (actual time=2,987.971..5,423.767 rows=32,934 loops=1)

  • Join Filter: (dm.market_id = m.market_id)
8. 58.553 5,292.258 ↓ 10,978.0 32,934 1

Nested Loop (cost=643.01..32,524.26 rows=3 width=37) (actual time=2,987.955..5,292.258 rows=32,934 loops=1)

9. 46.480 5,167.837 ↓ 2,744.5 32,934 1

Nested Loop (cost=642.73..32,520.71 rows=12 width=28) (actual time=2,987.941..5,167.837 rows=32,934 loops=1)

10. 43.029 5,022.555 ↓ 23.7 32,934 1

Hash Join (cost=642.44..32,086.82 rows=1,391 width=20) (actual time=2,987.915..5,022.555 rows=32,934 loops=1)

  • Hash Cond: ((ac.domain_id = k.domain_id) AND (ac.keyword_id = k.keyword_id))
11. 4,962.198 4,962.198 ↑ 30.7 40,560 1

CTE Scan on all_collected ac (cost=0.00..24,906.44 rows=1,245,322 width=16) (actual time=2,970.508..4,962.198 rows=40,560 loops=1)

12. 8.572 17.328 ↑ 1.0 17,596 1

Hash (cost=378.50..378.50 rows=17,596 width=8) (actual time=17.328..17.328 rows=17,596 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 944kB
13. 8.756 8.756 ↑ 1.0 17,596 1

Seq Scan on domains_keywords k (cost=0.00..378.50 rows=17,596 width=8) (actual time=0.023..8.756 rows=17,596 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1954
14. 98.802 98.802 ↑ 1.0 1 32,934

Index Scan using domains_markets_pkey on domains_markets dm (cost=0.29..0.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=32,934)

  • Index Cond: ((domain_id = ac.domain_id) AND (market_id = ac.market_id))
  • Filter: (NOT deleted)
15. 65.868 65.868 ↑ 1.0 1 32,934

Index Scan using domains_pkey on domains d (cost=0.28..0.30 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=32,934)

  • Index Cond: (domain_id = ac.domain_id)
16. 65.868 65.868 ↑ 1.0 1 32,934

Index Scan using markets_pkey on markets m (cost=0.29..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=32,934)

  • Index Cond: (market_id = ac.market_id)
  • Filter: (parent_id IS NULL)
17.          

CTE rankings

18. 73.335 6,177.040 ↓ 10,978.0 32,934 1

Nested Loop Left Join (cost=1.12..51.64 rows=3 width=44) (actual time=2,988.078..6,177.040 rows=32,934 loops=1)

19. 52.579 5,741.431 ↓ 10,978.0 32,934 1

Nested Loop Left Join (cost=0.56..25.82 rows=3 width=26) (actual time=2,988.060..5,741.431 rows=32,934 loops=1)

20. 5,458.314 5,458.314 ↓ 10,978.0 32,934 1

CTE Scan on collected c (cost=0.00..0.06 rows=3 width=48) (actual time=2,987.974..5,458.314 rows=32,934 loops=1)

21. 230.538 230.538 ↑ 1.0 1 32,934

Index Scan using multisample_rankings_domain_id_market_id_keyword_id_domain__idx on multisample_rankings r (cost=0.56..8.59 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=32,934)

  • Index Cond: ((c.domain_id = domain_id) AND (c.market_id = market_id) AND (c.keyword_id = keyword_id) AND (c.domain = domain) AND (c.date = date))
22. 65.868 362.274 ↑ 1.0 1 32,934

Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=32,934)

23. 296.406 296.406 ↑ 1.0 1 32,934

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.009..0.009 rows=1 loops=32,934)

  • Index Cond: ((c.domain_id = domain_id) AND (c.market_id = market_id) AND (c.keyword_id = keyword_id) AND (c.date = date))
24. 30.861 6,258.788 ↑ 1.0 3 1

HashAggregate (cost=0.14..0.29 rows=3 width=84) (actual time=6,257.936..6,258.788 rows=3 loops=1)

  • Group Key: date_part('isoyear'::text, (rankings.date)::timestamp without time zone), date_part('week'::text, (rankings.date)::timestamp without time zone)
25. 6,227.927 6,227.927 ↓ 10,978.0 32,934 1

CTE Scan on rankings (cost=0.00..0.09 rows=3 width=60) (actual time=2,988.087..6,227.927 rows=32,934 loops=1)

Planning time : 5.096 ms
Execution time : 6,269.623 ms