explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9U2F

Settings
# exclusive inclusive rows x rows loops node
1. 15.229 8,463.383 ↓ 5.0 20 1

WindowAgg (cost=1,244,734.52..1,244,735.65 rows=4 width=124) (actual time=8,462.248..8,463.383 rows=20 loops=1)

2.          

CTE domain_params

3. 41.819 402.614 ↑ 4.8 19,904 1

Nested Loop (cost=652.80..826,145.67 rows=95,518 width=16) (actual time=0.611..402.614 rows=19,904 loops=1)

4. 16.441 82.139 ↑ 4.8 19,904 1

Nested Loop (cost=652.24..3,974.49 rows=95,518 width=12) (actual time=0.578..82.139 rows=19,904 loops=1)

5. 0.973 1.010 ↑ 1.1 311 1

Bitmap Heap Scan on domains_keywords dk (cost=11.69..347.45 rows=352 width=8) (actual time=0.056..1.010 rows=311 loops=1)

  • Recheck Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 131
  • Heap Blocks: exact=122
6. 0.037 0.037 ↑ 1.0 442 1

Bitmap Index Scan on domains_keywords_pkey (cost=0.00..11.60 rows=442 width=0) (actual time=0.037..0.037 rows=442 loops=1)

  • Index Cond: (domain_id = 725)
7. 8.651 64.688 ↑ 4.2 64 311

Materialize (cost=640.55..2,435.32 rows=271 width=8) (actual time=0.002..0.208 rows=64 loops=311)

8. 0.055 56.037 ↑ 4.2 64 1

Nested Loop (cost=640.55..2,433.96 rows=271 width=8) (actual time=0.519..56.037 rows=64 loops=1)

9. 0.014 0.014 ↑ 1.0 1 1

Index Only Scan using domains_pkey on domains d (cost=0.28..8.30 rows=1 width=4) (actual time=0.011..0.014 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
  • Heap Fetches: 1
10. 23.880 55.968 ↑ 4.2 64 1

Hash Join (cost=640.27..2,422.96 rows=271 width=8) (actual time=0.506..55.968 rows=64 loops=1)

  • Hash Cond: (m.market_id = dm.market_id)
11. 31.689 31.689 ↑ 1.0 58,698 1

Seq Scan on markets m (cost=0.00..1,628.40 rows=58,772 width=4) (actual time=0.009..31.689 rows=58,698 loops=1)

  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 11062
12. 0.177 0.399 ↓ 1.2 383 1

Hash (cost=636.24..636.24 rows=322 width=8) (actual time=0.399..0.399 rows=383 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
13. 0.199 0.222 ↓ 1.2 383 1

Bitmap Heap Scan on domains_markets dm (cost=11.19..636.24 rows=322 width=8) (actual time=0.031..0.222 rows=383 loops=1)

  • Recheck Cond: (domain_id = 725)
  • Filter: (NOT deleted)
  • Heap Blocks: exact=6
14. 0.023 0.023 ↓ 1.0 383 1

Bitmap Index Scan on domains_markets_pkey (cost=0.00..11.11 rows=376 width=0) (actual time=0.023..0.023 rows=383 loops=1)

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

Subquery Scan on dt (cost=0.56..8.60 rows=1 width=4) (actual time=0.012..0.014 rows=1 loops=19,904)

  • Filter: (dt.date IS NOT NULL)
16. 39.808 258.752 ↑ 1.0 1 19,904

Limit (cost=0.56..8.59 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=19,904)

17. 218.944 218.944 ↑ 1.0 1 19,904

Index Only Scan Backward using multisample_rankings_pkey on multisample_rankings (cost=0.56..8.59 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=19,904)

  • Index Cond: ((domain_id = d.domain_id) AND (keyword_id = dk.keyword_id) AND (market_id = dm.market_id))
  • Heap Fetches: 0
18.          

CTE serp_competitors

19. 0.022 8,445.715 ↓ 6.7 20 1

Limit (cost=418,514.07..418,514.08 rows=3 width=115) (actual time=8,445.686..8,445.715 rows=20 loops=1)

20. 9.320 8,445.693 ↓ 6.7 20 1

Sort (cost=418,514.07..418,514.08 rows=3 width=115) (actual time=8,445.685..8,445.693 rows=20 loops=1)

  • Sort Key: (f_wilson(array_remove(array_agg(mr.avg_rank), NULL::numeric))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 211kB
21. 3,921.347 8,436.373 ↓ 3,090.3 9,271 1

GroupAggregate (cost=418,513.18..418,514.05 rows=3 width=115) (actual time=3,945.091..8,436.373 rows=9,271 loops=1)

  • Group Key: mr.domain
22. 1,823.706 4,515.026 ↓ 341,929.7 1,025,789 1

Sort (cost=418,513.18..418,513.19 rows=3 width=33) (actual time=3,944.816..4,515.026 rows=1,025,789 loops=1)

  • Sort Key: mr.domain
  • Sort Method: external merge Disk: 47840kB
23. 815.460 2,691.320 ↓ 341,929.7 1,025,789 1

Nested Loop (cost=0.56..418,513.16 rows=3 width=33) (actual time=0.638..2,691.320 rows=1,025,789 loops=1)

24. 422.868 422.868 ↑ 4.8 19,904 1

CTE Scan on domain_params (cost=0.00..1,910.36 rows=95,518 width=16) (actual time=0.613..422.868 rows=19,904 loops=1)

25. 1,452.992 1,452.992 ↓ 52.0 52 19,904

Index Only Scan using multisample_rankings_domain_id_market_id_keyword_id_domain__idx on multisample_rankings mr (cost=0.56..4.35 rows=1 width=37) (actual time=0.010..0.073 rows=52 loops=19,904)

  • Index Cond: ((domain_id = domain_params.domain_id) AND (market_id = domain_params.market_id) AND (keyword_id = domain_params.keyword_id) AND (date = domain_params.date))
  • Heap Fetches: 0
26.          

CTE tracked_urls

27. 0.015 0.078 ↓ 1.1 8 1

HashAggregate (cost=32.27..32.34 rows=7 width=64) (actual time=0.073..0.078 rows=8 loops=1)

  • Group Key: domains.name, ('You'::character varying)
28. 0.009 0.063 ↓ 1.1 8 1

Append (cost=0.28..32.24 rows=7 width=64) (actual time=0.020..0.063 rows=8 loops=1)

29. 0.019 0.019 ↑ 1.0 1 1

Index Scan using domains_pkey on domains (cost=0.28..8.30 rows=1 width=49) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
30. 0.035 0.035 ↓ 1.2 7 1

Index Scan using competitors_domain_id_url_key on competitors (cost=0.28..23.84 rows=6 width=19) (actual time=0.013..0.035 rows=7 loops=1)

  • Index Cond: (domain_id = 725)
  • Filter: (NOT deleted)
31.          

CTE tracked_competitors_and_site

32. 0.015 0.212 ↑ 1.0 1 1

Hash Join (cost=0.08..0.26 rows=1 width=164) (actual time=0.194..0.212 rows=1 loops=1)

  • Hash Cond: (t.competitor = serp_competitors.competitor)
33. 0.088 0.088 ↓ 1.1 8 1

CTE Scan on tracked_urls t (cost=0.00..0.14 rows=7 width=64) (actual time=0.075..0.088 rows=8 loops=1)

34. 0.061 0.109 ↓ 20.0 20 1

Hash (cost=0.07..0.07 rows=1 width=132) (actual time=0.109..0.109 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 183kB
35. 0.048 0.048 ↓ 20.0 20 1

CTE Scan on serp_competitors (cost=0.00..0.07 rows=1 width=132) (actual time=0.034..0.048 rows=20 loops=1)

  • Filter: (avg_rank < '20'::numeric)
36.          

CTE all_competitors

37. 0.068 8,447.622 ↓ 5.0 20 1

Sort (cost=0.28..0.29 rows=4 width=164) (actual time=8,447.614..8,447.622 rows=20 loops=1)

  • Sort Key: serp_competitors_1.wilson DESC NULLS LAST
  • Sort Method: quicksort Memory: 207kB
38. 1.497 8,447.554 ↓ 5.0 20 1

HashAggregate (cost=0.20..0.24 rows=4 width=164) (actual time=8,447.543..8,447.554 rows=20 loops=1)

  • Group Key: serp_competitors_1.competitor, serp_competitors_1.date, serp_competitors_1.avg_rank, serp_competitors_1.wilson, serp_competitors_1.ranks, (NULL::character varying)
39. 0.019 8,446.057 ↓ 5.2 21 1

Append (cost=0.00..0.14 rows=4 width=164) (actual time=8,445.729..8,446.057 rows=21 loops=1)

40. 8,445.784 8,445.784 ↓ 6.7 20 1

CTE Scan on serp_competitors serp_competitors_1 (cost=0.00..0.06 rows=3 width=164) (actual time=8,445.727..8,445.784 rows=20 loops=1)

41. 0.254 0.254 ↑ 1.0 1 1

CTE Scan on tracked_competitors_and_site (cost=0.00..0.02 rows=1 width=164) (actual time=0.235..0.254 rows=1 loops=1)

42.          

CTE all_competitors_with_our_ranks

43. 0.000 8,447.830 ↓ 5.0 20 1

Unique (cost=8.52..8.54 rows=4 width=196) (actual time=8,447.786..8,447.830 rows=20 loops=1)

44.          

Initplan (for Unique)

45. 0.000 0.062 ↓ 0.0 0 1

Unique (cost=8.40..8.40 rows=1 width=96) (actual time=0.061..0.062 rows=0 loops=1)

46.          

Initplan (for Unique)

47. 0.010 0.010 ↑ 1.0 1 1

Index Scan using domains_pkey on domains domains_1 (cost=0.28..8.30 rows=1 width=17) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (domain_id = 725)
48. 0.008 0.060 ↓ 0.0 0 1

Sort (cost=0.10..0.11 rows=1 width=96) (actual time=0.060..0.060 rows=0 loops=1)

  • Sort Key: all_competitors.tag
  • Sort Method: quicksort Memory: 25kB
49. 0.052 0.052 ↓ 0.0 0 1

CTE Scan on all_competitors (cost=0.00..0.09 rows=1 width=96) (actual time=0.051..0.052 rows=0 loops=1)

  • Filter: (competitor = $12)
  • Rows Removed by Filter: 20
50. 0.059 8,447.794 ↓ 5.0 20 1

Sort (cost=0.12..0.13 rows=4 width=196) (actual time=8,447.785..8,447.794 rows=20 loops=1)

  • Sort Key: all_competitors_1.competitor, all_competitors_1.tag
  • Sort Method: quicksort Memory: 207kB
51. 8,447.735 8,447.735 ↓ 5.0 20 1

CTE Scan on all_competitors all_competitors_1 (cost=0.00..0.08 rows=4 width=196) (actual time=8,447.719..8,447.735 rows=20 loops=1)

52. 0.058 8,448.154 ↓ 5.0 20 1

Sort (cost=33.34..33.35 rows=4 width=200) (actual time=8,448.145..8,448.154 rows=20 loops=1)

  • Sort Key: a.wilson DESC NULLS LAST
  • Sort Method: quicksort Memory: 207kB
53. 0.043 8,448.096 ↓ 5.0 20 1

Nested Loop Left Join (cost=0.29..33.30 rows=4 width=200) (actual time=8,447.803..8,448.096 rows=20 loops=1)

54. 8,447.893 8,447.893 ↓ 5.0 20 1

CTE Scan on all_competitors_with_our_ranks a (cost=0.00..0.08 rows=4 width=196) (actual time=8,447.789..8,447.893 rows=20 loops=1)

55. 0.160 0.160 ↑ 1.0 1 20

Index Scan using idx_domain_authority_url on domain_authority da (cost=0.29..8.30 rows=1 width=21) (actual time=0.008..0.008 rows=1 loops=20)

  • Index Cond: (url = a.competitor)
Planning time : 1.182 ms
Execution time : 8,471.107 ms