explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jKyg

Settings
# exclusive inclusive rows x rows loops node
1. 15,714.159 540,432.326 ↓ 6.9 18,188,432 1

Merge Right Join (cost=14,098,464.27..14,319,412.07 rows=2,638,362 width=244) (actual time=490,603.445..540,432.326 rows=18,188,432 loops=1)

  • Merge Cond: ((ga_grouped.date = sct_grouped.date) AND (ga_grouped.site = sct_grouped.site) AND (ga_grouped.link = sct_grouped.link) AND (ga_grouped.page_number = sct_grouped.card_no) AND (ga_grouped.device = sct_grouped.device) AND (ga_grouped.channel = sct_grouped.channel))
2.          

CTE ga_grouped

3. 20,783.557 208,355.656 ↑ 1.1 8,659,205 1

Finalize GroupAggregate (cost=5,106,267.60..7,427,347.60 rows=9,610,317 width=224) (actual time=154,345.074..208,355.656 rows=8,659,205 loops=1)

  • Group Key: ga_story_performance_page_path.date, ga_story_performance_page_path.link, ga_story_performance_page_path.site, ga_story_performance_page_path.device, ga_story_performance_page_path.page_number, flaf.channel
4. 9,101.538 187,572.099 ↑ 1.1 8,751,087 1

Gather Merge (cost=5,106,267.60..6,826,702.79 rows=9,610,317 width=147) (actual time=154,324.724..187,572.099 rows=8,751,087 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 13,345.490 178,470.561 ↑ 2.2 4,375,544 2 / 2

Partial GroupAggregate (cost=5,105,267.59..5,744,542.12 rows=9,610,317 width=147) (actual time=153,611.011..178,470.561 rows=4,375,544 loops=2)

  • Group Key: ga_story_performance_page_path.date, ga_story_performance_page_path.link, ga_story_performance_page_path.site, ga_story_performance_page_path.device, ga_story_performance_page_path.page_number, flaf.channel
6. 145,004.769 165,125.071 ↑ 1.2 16,031,540 2 / 2

Sort (cost=5,105,267.59..5,152,376.99 rows=18,843,759 width=75) (actual time=153,588.108..165,125.071 rows=16,031,540 loops=2)

  • Sort Key: ga_story_performance_page_path.date, ga_story_performance_page_path.link, ga_story_performance_page_path.site, ga_story_performance_page_path.device, ga_story_performance_page_path.page_number, flaf.channel
  • Sort Method: external merge Disk: 1,439,840kB
  • Worker 0: Sort Method: external merge Disk: 1,447,696kB
7. 8,916.047 20,120.302 ↑ 1.2 16,031,540 2 / 2

Hash Left Join (cost=33.40..1,356,974.80 rows=18,843,759 width=75) (actual time=19.755..20,120.302 rows=16,031,540 loops=2)

  • Hash Cond: (ga_story_performance_page_path.flaf_id = flaf.pid)
8. 11,204.083 11,204.083 ↑ 1.2 16,021,964 2 / 2

Parallel Seq Scan on ga_story_performance_page_path (cost=0.00..957,884.99 rows=18,843,759 width=71) (actual time=19.522..11,204.083 rows=16,021,964 loops=2)

  • Filter: (date >= '2020-05-28'::date)
9. 0.087 0.172 ↑ 3.5 298 2 / 2

Hash (cost=20.40..20.40 rows=1,040 width=16) (actual time=0.170..0.172 rows=298 loops=2)

  • Buckets: 2,048 Batches: 1 Memory Usage: 31kB
10. 0.085 0.085 ↑ 3.5 299 2 / 2

Seq Scan on flaf (cost=0.00..20.40 rows=1,040 width=16) (actual time=0.017..0.085 rows=299 loops=2)

11.          

CTE sct_grouped

12. 12,157.171 121,670.725 ↓ 6.9 18,179,147 1

Finalize GroupAggregate (cost=2,244,809.68..2,893,025.31 rows=2,638,362 width=56) (actual time=84,165.602..121,670.725 rows=18,179,147 loops=1)

  • Group Key: story_card_tracking_dashboard.date, story_card_tracking_dashboard.site, story_card_tracking_dashboard.link, story_card_tracking_dashboard.card_no, story_card_tracking_dashboard.device, story_card_tracking_dashboard.channel
13. 14,295.728 109,513.554 ↓ 6.9 18,182,530 1

Gather Merge (cost=2,244,809.68..2,813,874.45 rows=2,638,362 width=56) (actual time=84,163.439..109,513.554 rows=18,182,530 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
14. 5,986.213 95,217.826 ↓ 3.4 9,091,265 2 / 2

Partial GroupAggregate (cost=2,243,809.67..2,516,058.71 rows=2,638,362 width=56) (actual time=83,358.271..95,217.826 rows=9,091,265 loops=2)

  • Group Key: story_card_tracking_dashboard.date, story_card_tracking_dashboard.site, story_card_tracking_dashboard.link, story_card_tracking_dashboard.card_no, story_card_tracking_dashboard.device, story_card_tracking_dashboard.channel
15. 77,543.310 89,231.613 ↑ 1.2 9,427,884 2 / 2

Sort (cost=2,243,809.67..2,271,128.05 rows=10,927,352 width=52) (actual time=83,354.945..89,231.613 rows=9,427,884 loops=2)

  • Sort Key: story_card_tracking_dashboard.date, story_card_tracking_dashboard.site, story_card_tracking_dashboard.link, story_card_tracking_dashboard.card_no, story_card_tracking_dashboard.device, story_card_tracking_dashboard.channel
  • Sort Method: external merge Disk: 611,648kB
  • Worker 0: Sort Method: external merge Disk: 620,016kB
16. 11,688.303 11,688.303 ↑ 1.2 9,427,884 2 / 2

Parallel Seq Scan on story_card_tracking_dashboard (cost=0.00..528,800.22 rows=10,927,352 width=52) (actual time=5,460.860..11,688.303 rows=9,427,884 loops=2)

  • Filter: (date >= '2020-05-28'::date)
  • Rows Removed by Filter: 3,774,736
17. 85,143.229 305,591.692 ↑ 1.1 8,641,498 1

Sort (cost=3,211,522.44..3,235,548.23 rows=9,610,317 width=236) (actual time=293,737.491..305,591.692 rows=8,641,498 loops=1)

  • Sort Key: ga_grouped.date, ga_grouped.site, ga_grouped.link, ga_grouped.page_number, ga_grouped.device, ga_grouped.channel
  • Sort Method: external merge Disk: 713,904kB
18. 220,448.463 220,448.463 ↑ 1.1 8,659,205 1

CTE Scan on ga_grouped (cost=0.00..192,206.34 rows=9,610,317 width=236) (actual time=154,349.351..220,448.463 rows=8,659,205 loops=1)

19. 2,974.601 219,126.475 ↓ 6.9 18,188,432 1

Materialize (cost=566,568.92..579,760.73 rows=2,638,362 width=148) (actual time=196,855.728..219,126.475 rows=18,188,432 loops=1)

20. 81,255.437 216,151.874 ↓ 6.9 18,179,147 1

Sort (cost=566,568.92..573,164.83 rows=2,638,362 width=148) (actual time=196,853.893..216,151.874 rows=18,179,147 loops=1)

  • Sort Key: sct_grouped.date, sct_grouped.site, sct_grouped.link, sct_grouped.card_no, sct_grouped.device, sct_grouped.channel
  • Sort Method: external merge Disk: 1,293,288kB
21. 134,896.437 134,896.437 ↓ 6.9 18,179,147 1

CTE Scan on sct_grouped (cost=0.00..52,767.24 rows=2,638,362 width=148) (actual time=84,168.147..134,896.437 rows=18,179,147 loops=1)