explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rcjo

Settings
# exclusive inclusive rows x rows loops node
1. 119,385.919 119,385.919 ↑ 1.9 7,203 1

CTE Scan on first_query (cost=5,537,107.17..5,538,207.17 rows=13,333 width=104) (actual time=119,357.279..119,385.919 rows=7,203 loops=1)

  • Filter: (campaign_start_date <= (CURRENT_DATE - '30 days'::interval))
  • Rows Removed by Filter: 1,673
2.          

CTE yahoo_performance_stats_campaigns_combined

3. 4,358.537 112,884.551 ↓ 1.0 8,421,669 1

Unique (cost=4,283,442.51..4,872,523.45 rows=8,415,442 width=396) (actual time=89,756.682..112,884.551 rows=8,421,669 loops=1)

4. 103,173.988 108,526.014 ↓ 1.0 8,421,669 1

Sort (cost=4,283,442.51..4,304,481.11 rows=8,415,442 width=396) (actual time=89,755.029..108,526.014 rows=8,421,669 loops=1)

  • Sort Key: yahoo_performance_stats_campaigns.advertiser_id, yahoo_performance_stats_campaigns.day, yahoo_performance_stats_campaigns.advertiser_name, yahoo_performance_stats_campaigns.advertiser_timezone, yahoo_performance_stats_campaigns.advertiser_currency, yahoo_performance_stats_campaigns.campaign_id, yahoo_performance_stats_campaigns.campaign_name, yahoo_performance_stats_campaigns.campaign_start_date, yahoo_performance_stats_campaigns.campaign_end_date, yahoo_performance_stats_campaigns.campaign_status, yahoo_performance_stats_campaigns.campaign_objective, yahoo_performance_stats_campaigns.budget, yahoo_performance_stats_campaigns.budget_type, yahoo_performance_stats_campaigns.impressions, yahoo_performance_stats_campaigns.clicks, yahoo_performance_stats_campaigns.ctr, yahoo_performance_stats_campaigns.conversions, yahoo_performance_stats_campaigns.spend, yahoo_performance_stats_campaigns.average_cpc, yahoo_performance_stats_campaigns.""average_cost-per-install"", yahoo_performance_stats_campaigns.average_cpm, yahoo_performance_stats_campaigns.pricing_type, yahoo_performance_stats_campaigns.source, yahoo_performance_stats_campaigns.average_position, yahoo_performance_stats_campaigns.max_bid, yahoo_performance_stats_campaigns.""timestamp"", yahoo_performance_stats_campaigns.fetch_time
  • Sort Method: external merge Disk: 2,880,384kB
5. 652.098 5,352.026 ↓ 1.0 8,421,669 1

Append (cost=0.00..636,748.05 rows=8,415,442 width=396) (actual time=0.927..5,352.026 rows=8,421,669 loops=1)

6. 4,681.422 4,681.422 ↓ 1.0 8,408,596 1

Seq Scan on yahoo_performance_stats_campaigns (cost=0.00..509,186.69 rows=8,402,369 width=321) (actual time=0.926..4,681.422 rows=8,408,596 loops=1)

7. 18.506 18.506 ↑ 1.0 13,073 1

Seq Scan on yahoo_performance_stats_campaigns yahoo_performance_stats_campaigns_1 (cost=0.00..1,329.73 rows=13,073 width=327) (actual time=1.440..18.506 rows=13,073 loops=1)

8.          

CTE first_query

9. 16.570 119,369.155 ↑ 4.5 8,876 1

GroupAggregate (cost=621,706.52..664,583.72 rows=40,000 width=108) (actual time=119,356.282..119,369.155 rows=8,876 loops=1)

  • Group Key: yahoo_performance_stats_campaigns_combined.campaign_start_date, yahoo_performance_stats_campaigns_combined.campaign_id
10. 165.763 119,352.585 ↑ 122.6 22,874 1

Sort (cost=621,706.52..628,719.39 rows=2,805,147 width=36) (actual time=119,351.106..119,352.585 rows=22,874 loops=1)

  • Sort Key: yahoo_performance_stats_campaigns_combined.campaign_start_date, yahoo_performance_stats_campaigns_combined.campaign_id
  • Sort Method: quicksort Memory: 2,556kB
11. 119,186.822 119,186.822 ↑ 122.6 22,874 1

CTE Scan on yahoo_performance_stats_campaigns_combined (cost=0.00..231,424.66 rows=2,805,147 width=36) (actual time=90,296.764..119,186.822 rows=22,874 loops=1)

  • Filter: (day >= (CURRENT_DATE - '3 days'::interval))
  • Rows Removed by Filter: 8,398,795