explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P2gu

Settings
# exclusive inclusive rows x rows loops node
1. 14,794.902 858,805.624 ↓ 8.5 44,113,935 1

Merge Right Join (cost=45,588,263.54..45,807,631.90 rows=5,194,867 width=244) (actual time=832,655.856..858,805.624 rows=44,113,935 loops=1)

  • Merge Cond: ((ga.date = sct.date) AND (ga.site = sct.site) AND (ga.link = sct.link) AND (ga.device = sct.device) AND (ga.channel = sct.channel))
2.          

CTE ga

3. 14,757.792 461,199.545 ↑ 1.2 7,332,033 1

Finalize GroupAggregate (cost=25,643,148.54..29,496,852.77 rows=8,563,787 width=193) (actual time=398,361.398..461,199.545 rows=7,332,033 loops=1)

  • Group Key: ""*SELECT* 1"".date, ""*SELECT* 1"".link, ""*SELECT* 1"".site, ""*SELECT* 1"".device, (COALESCE(""*SELECT* 1"".sub_channel, ""*SELECT* 1"".channel))
4. 30,927.647 446,441.753 ↑ 1.1 7,586,649 1

Gather Merge (cost=25,643,148.54..29,111,482.36 rows=8,563,787 width=193) (actual time=398,361.357..446,441.753 rows=7,586,649 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 23,365.820 415,514.106 ↑ 2.3 3,793,324 2 / 2

Partial GroupAggregate (cost=25,642,148.53..28,147,056.31 rows=8,563,787 width=193) (actual time=381,038.619..415,514.106 rows=3,793,324 loops=2)

  • Group Key: ""*SELECT* 1"".date, ""*SELECT* 1"".link, ""*SELECT* 1"".site, ""*SELECT* 1"".device, (COALESCE(""*SELECT* 1"".sub_channel, ""*SELECT* 1"".channel))
6. 122,454.179 392,148.286 ↑ 2.0 42,998,642 2 / 2

Sort (cost=25,642,148.53..25,856,243.21 rows=85,637,873 width=129) (actual time=381,038.585..392,148.286 rows=42,998,642 loops=2)

  • Sort Key: ""*SELECT* 1"".date, ""*SELECT* 1"".link, ""*SELECT* 1"".site, ""*SELECT* 1"".device, (COALESCE(""*SELECT* 1"".sub_channel, ""*SELECT* 1"".channel))
  • Sort Method: external merge Disk: 5,392,840kB
  • Worker 0: Sort Method: external merge Disk: 2,159,664kB
7. 5,738.161 269,694.107 ↑ 2.0 42,998,642 2 / 2

Result (cost=11.97..7,500,889.80 rows=85,637,873 width=129) (actual time=3.158..269,694.107 rows=42,998,642 loops=2)

8. 3,735.254 263,955.946 ↑ 2.0 42,998,642 2 / 2

Parallel Append (cost=11.97..6,644,511.07 rows=85,637,873 width=112) (actual time=3.156..263,955.946 rows=42,998,642 loops=2)

9. 2,780.075 239,246.341 ↑ 2.0 20,859,434 2 / 2

Subquery Scan on "*SELECT* 1" (cost=11.97..3,508,463.00 rows=41,545,327 width=116) (actual time=3.049..239,246.341 rows=20,859,434 loops=2)

10. 9,911.541 236,466.266 ↑ 1.2 20,859,434 2 / 2

Hash Left Join (cost=11.97..3,093,009.73 rows=24,438,428 width=224) (actual time=3.048..236,466.266 rows=20,859,434 loops=2)

  • Hash Cond: (ga_story_performance.flaf_id = flaf.pid)
11. 226,554.592 226,554.592 ↑ 1.2 20,859,416 2 / 2

Parallel Index Scan using idx_1725299_date on ga_story_performance (cost=0.57..2,359,849.36 rows=24,437,653 width=80) (actual time=2.873..226,554.592 rows=20,859,416 loops=2)

  • Index Cond: ((date < '2020-05-28'::date) AND (date >= '2019-07-01'::date))
12. 0.065 0.133 ↑ 1.0 326 2 / 2

Hash (cost=7.29..7.29 rows=329 width=23) (actual time=0.132..0.133 rows=326 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
13. 0.068 0.068 ↑ 1.0 329 2 / 2

Seq Scan on flaf (cost=0.00..7.29 rows=329 width=23) (actual time=0.011..0.068 rows=329 loops=2)

14. 2,808.600 20,974.351 ↓ 1.0 44,278,415 1 / 2

Subquery Scan on "*SELECT* 2" (cost=11.40..2,707,858.71 rows=44,092,546 width=108) (actual time=0.990..41,948.702 rows=44,278,415 loops=1)

15. 9,775.735 18,165.751 ↓ 1.7 44,278,415 1 / 2

Hash Left Join (cost=11.40..2,266,933.25 rows=25,936,792 width=216) (actual time=0.989..36,331.502 rows=44,278,415 loops=1)

  • Hash Cond: (ga_story_performance_page_path.flaf_id = flaf_1.pid)
16. 8,389.925 8,389.925 ↓ 1.7 44,250,106 1 / 2

Parallel Seq Scan on ga_story_performance_page_path (cost=0.00..1,488,885.04 rows=25,923,402 width=71) (actual time=0.787..16,779.851 rows=44,250,106 loops=1)

  • Filter: ((date >= '2020-05-28'::date) AND (date >= '2019-07-01'::date))
17. 0.035 0.090 ↑ 1.0 326 1 / 2

Hash (cost=7.29..7.29 rows=329 width=23) (actual time=0.181..0.181 rows=326 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
18. 0.056 0.056 ↑ 1.0 329 1 / 2

Seq Scan on flaf flaf_1 (cost=0.00..7.29 rows=329 width=23) (actual time=0.020..0.111 rows=329 loops=1)

19.          

CTE sct

20. 17,693.707 222,879.499 ↓ 8.5 44,113,935 1

GroupAggregate (cost=11,475,044.48..12,695,838.14 rows=5,194,867 width=57) (actual time=197,025.149..222,879.499 rows=44,113,935 loops=1)

  • Group Key: ""*SELECT* 1_1"".date, ""*SELECT* 1_1"".site, ""*SELECT* 1_1"".link, ""*SELECT* 1_1"".card_no, ""*SELECT* 1_1"".device, ""*SELECT* 1_1"".channel
21. 162,499.458 205,185.792 ↓ 1.0 51,952,873 1

Sort (cost=11,475,044.48..11,604,916.15 rows=51,948,666 width=53) (actual time=197,025.130..205,185.792 rows=51,952,873 loops=1)

  • Sort Key: ""*SELECT* 1_1"".date, ""*SELECT* 1_1"".site, ""*SELECT* 1_1"".link, ""*SELECT* 1_1"".card_no, ""*SELECT* 1_1"".device, ""*SELECT* 1_1"".channel
  • Sort Method: external merge Disk: 3,431,120kB
22. 3,790.181 42,686.334 ↓ 1.0 51,952,873 1

Append (cost=0.00..2,737,692.21 rows=51,948,666 width=53) (actual time=86.338..42,686.334 rows=51,952,873 loops=1)

23. 3,216.221 12,337.294 ↓ 1.0 32,325,963 1

Subquery Scan on "*SELECT* 1_1" (cost=0.00..1,227,733.57 rows=32,325,680 width=52) (actual time=86.336..12,337.294 rows=32,325,963 loops=1)

24. 9,121.073 9,121.073 ↓ 1.0 32,325,963 1

Seq Scan on story_card_tracking_dashboard (cost=0.00..904,476.77 rows=32,325,680 width=64) (actual time=86.334..9,121.073 rows=32,325,963 loops=1)

  • Filter: ((date >= '2020-03-08'::date) AND (date >= '2019-07-01'::date))
  • Rows Removed by Filter: 459,264
25. 2,297.687 26,558.859 ↓ 1.0 19,626,910 1

Subquery Scan on "*SELECT* 2_1" (cost=0.00..1,250,215.31 rows=19,622,986 width=54) (actual time=1.736..26,558.859 rows=19,626,910 loops=1)

26. 24,261.172 24,261.172 ↓ 1.0 19,626,910 1

Seq Scan on fb_spa_scroll_tracking (cost=0.00..1,053,985.45 rows=19,622,986 width=66) (actual time=1.735..24,261.172 rows=19,626,910 loops=1)

  • Filter: ((date < '2020-03-08'::date) AND (event_label ~ '^[0-9]+$'::text) AND (max_slide ~ '^[0-9.]+$'::text) AND (date >= '2019-07-01'::date))
  • Rows Removed by Filter: 319,771
27. 47,327.056 515,303.885 ↑ 1.2 7,326,821 1

Sort (cost=2,254,625.90..2,276,035.37 rows=8,563,787 width=228) (actual time=513,607.979..515,303.885 rows=7,326,821 loops=1)

  • Sort Key: ga.date, ga.site, ga.link, ga.device, ga.channel
  • Sort Method: external merge Disk: 555,400kB
28. 467,976.829 467,976.829 ↑ 1.2 7,332,033 1

CTE Scan on ga (cost=0.00..171,275.74 rows=8,563,787 width=228) (actual time=398,361.402..467,976.829 rows=7,332,033 loops=1)

29. 4,207.139 328,706.837 ↓ 8.5 44,113,935 1

Materialize (cost=1,140,946.73..1,166,921.06 rows=5,194,867 width=148) (actual time=318,750.374..328,706.837 rows=44,113,935 loops=1)

30. 82,573.116 324,499.698 ↓ 8.5 44,113,935 1

Sort (cost=1,140,946.73..1,153,933.90 rows=5,194,867 width=148) (actual time=318,750.369..324,499.698 rows=44,113,935 loops=1)

  • Sort Key: sct.date, sct.site, sct.link, sct.device, sct.channel
  • Sort Method: external merge Disk: 3,165,784kB
31. 241,926.582 241,926.582 ↓ 8.5 44,113,935 1

CTE Scan on sct (cost=0.00..103,897.34 rows=5,194,867 width=148) (actual time=197,025.154..241,926.582 rows=44,113,935 loops=1)

Planning time : 38.245 ms