explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7EXt

Settings
# exclusive inclusive rows x rows loops node
1. 3.560 3,641.734 ↓ 38.0 913 1

Hash Join (cost=288,312.29..289,692.01 rows=24 width=552) (actual time=3,590.223..3,641.734 rows=913 loops=1)

  • Hash Cond: (yahoo_campaigns_combined.id = yahoo_performance_stats.campaign_id)
2.          

CTE yahoo_performance_stats_campaigns_combined

3. 258.792 2,482.414 ↓ 1.0 476,507 1

Unique (cost=198,218.13..230,817.55 rows=465,706 width=396) (actual time=2,078.541..2,482.414 rows=476,507 loops=1)

4. 1,958.889 2,223.622 ↓ 1.0 476,507 1

Sort (cost=198,218.13..199,382.40 rows=465,706 width=396) (actual time=2,078.540..2,223.622 rows=476,507 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: 166,424kB
5. 39.582 264.733 ↓ 1.0 476,507 1

Append (cost=0.44..55,547.70 rows=465,706 width=396) (actual time=0.026..264.733 rows=476,507 loops=1)

6. 225.124 225.124 ↓ 1.0 476,498 1

Index Scan using idx_527130_day on yahoo_performance_stats_campaigns (cost=0.44..48,551.70 rows=465,697 width=321) (actual time=0.025..225.124 rows=476,498 loops=1)

  • Index Cond: (day >= (CURRENT_DATE - '90 days'::interval))
  • Filter: (campaign_start_date <= (CURRENT_DATE - '30 days'::interval))
  • Rows Removed by Filter: 4,410
7. 0.027 0.027 ↑ 1.0 9 1

Index Scan using yahoo_performance_stats_campaigns_campaign_start_date_idx on yahoo_performance_stats_campaigns yahoo_performance_stats_campaigns_1 (cost=0.29..10.41 rows=9 width=327) (actual time=0.020..0.027 rows=9 loops=1)

  • Index Cond: (campaign_start_date <= (CURRENT_DATE - '30 days'::interval))
  • Filter: (day >= (CURRENT_DATE - '90 days'::interval))
8.          

CTE yahoo_campaigns_combined

9. 16.007 141.469 ↑ 1.0 42,394 1

Unique (cost=11,427.34..13,229.09 rows=42,394 width=344) (actual time=118.425..141.469 rows=42,394 loops=1)

10. 79.511 125.462 ↑ 1.0 42,394 1

Sort (cost=11,427.34..11,533.33 rows=42,394 width=344) (actual time=118.424..125.462 rows=42,394 loops=1)

  • Sort Key: yahoo_campaigns_2.advertiser_id, yahoo_campaigns_2.channel, yahoo_campaigns_2.conversion_rule_ids, yahoo_campaigns_2.created_date, yahoo_campaigns_2.language, yahoo_campaigns_2.status, yahoo_campaigns_2.sub_channel, yahoo_campaigns_2.fetch_time, yahoo_campaigns_2.campaign_name, yahoo_campaigns_2.budget, yahoo_campaigns_2.budget_type, yahoo_campaigns_2.id, yahoo_campaigns_2.last_update_date, yahoo_campaigns_2.objective, yahoo_campaigns_2.effective_status, yahoo_campaigns_2.""timestamp"
  • Sort Method: external merge Disk: 9,376kB
11. 3.910 45.951 ↑ 1.0 42,394 1

Append (cost=1,722.48..4,263.78 rows=42,394 width=344) (actual time=16.834..45.951 rows=42,394 loops=1)

12. 24.289 41.106 ↑ 1.0 40,278 1

Seq Scan on yahoo_campaigns yahoo_campaigns_2 (cost=1,722.48..3,444.96 rows=40,278 width=292) (actual time=16.833..41.106 rows=40,278 loops=1)

  • Filter: (""timestamp"" = $1)
13.          

Initplan (for Seq Scan)

14. 10.295 16.817 ↑ 1.0 1 1

Aggregate (cost=1,722.47..1,722.48 rows=1 width=8) (actual time=16.817..16.817 rows=1 loops=1)

15. 6.522 6.522 ↑ 1.0 40,278 1

Seq Scan on yahoo_campaigns (cost=0.00..1,621.78 rows=40,278 width=8) (actual time=0.002..6.522 rows=40,278 loops=1)

16. 0.410 0.935 ↑ 1.0 2,116 1

Seq Scan on yahoo_campaigns yahoo_campaigns_3 (cost=91.46..182.91 rows=2,116 width=293) (actual time=0.536..0.935 rows=2,116 loops=1)

  • Filter: (""timestamp"" = $2)
17.          

Initplan (for Seq Scan)

18. 0.308 0.525 ↑ 1.0 1 1

Aggregate (cost=91.45..91.46 rows=1 width=8) (actual time=0.524..0.525 rows=1 loops=1)

19. 0.217 0.217 ↑ 1.0 2,116 1

Seq Scan on yahoo_campaigns yahoo_campaigns_1 (cost=0.00..86.16 rows=2,116 width=8) (actual time=0.001..0.217 rows=2,116 loops=1)

20. 0.656 3,101.943 ↓ 49.8 1,195 1

Hash Join (cost=14,753.69..16,131.69 rows=24 width=192) (actual time=3,053.946..3,101.943 rows=1,195 loops=1)

  • Hash Cond: (yahoo_campaigns_combined.id = yahoo_performance_stats1.campaign_id)
21. 166.237 166.237 ↓ 18.9 1,342 1

CTE Scan on yahoo_campaigns_combined (cost=0.00..1,377.81 rows=71 width=88) (actual time=118.882..166.237 rows=1,342 loops=1)

  • Filter: ((effective_status = 'ACTIVE'::text) AND (date(created_date) < (CURRENT_DATE - '30 days'::interval)))
  • Rows Removed by Filter: 41,052
22. 3.364 2,935.050 ↓ 232.3 15,564 1

Hash (cost=14,752.86..14,752.86 rows=67 width=104) (actual time=2,935.050..2,935.050 rows=15,564 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,007kB
23. 2.220 2,931.686 ↓ 232.3 15,564 1

Subquery Scan on yahoo_performance_stats1 (cost=14,747.35..14,752.86 rows=67 width=104) (actual time=2,899.712..2,931.686 rows=15,564 loops=1)

24. 82.557 2,929.466 ↓ 232.3 15,564 1

HashAggregate (cost=14,747.35..14,752.19 rows=67 width=104) (actual time=2,899.710..2,929.466 rows=15,564 loops=1)

  • Group Key: yahoo_performance_stats_campaigns_combined.campaign_id
  • Filter: (round((sum(yahoo_performance_stats_campaigns_combined.spend))::numeric, 2) < '500'::numeric)
  • Rows Removed by Filter: 95
25. 2,846.909 2,846.909 ↑ 1.2 129,576 1

CTE Scan on yahoo_performance_stats_campaigns_combined (cost=0.00..12,806.92 rows=155,235 width=32) (actual time=2,084.559..2,846.909 rows=129,576 loops=1)

  • Filter: (day >= (CURRENT_DATE - '20 days'::interval))
  • Rows Removed by Filter: 346,931
26. 1.389 536.231 ↓ 101.0 6,767 1

Hash (cost=29,511.12..29,511.12 rows=67 width=208) (actual time=536.231..536.231 rows=6,767 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 620kB
27. 4.225 534.842 ↓ 101.0 6,767 1

Merge Join (cost=29,509.78..29,511.12 rows=67 width=208) (actual time=527.898..534.842 rows=6,767 loops=1)

  • Merge Cond: (yahoo_performance_stats.campaign_id = yahoo_performance_stats90.campaign_id)
28. 3.651 170.518 ↓ 101.0 6,767 1

Sort (cost=14,754.89..14,755.06 rows=67 width=104) (actual time=169.670..170.518 rows=6,767 loops=1)

  • Sort Key: yahoo_performance_stats.campaign_id
  • Sort Method: quicksort Memory: 721kB
29. 1.014 166.867 ↓ 101.0 6,767 1

Subquery Scan on yahoo_performance_stats (cost=14,747.35..14,752.86 rows=67 width=104) (actual time=150.214..166.867 rows=6,767 loops=1)

30. 21.489 165.853 ↓ 101.0 6,767 1

HashAggregate (cost=14,747.35..14,752.19 rows=67 width=104) (actual time=150.213..165.853 rows=6,767 loops=1)

  • Group Key: yahoo_performance_stats_campaigns_combined_1.campaign_id
  • Filter: (round((sum(yahoo_performance_stats_campaigns_combined_1.spend))::numeric, 2) < '10'::numeric)
  • Rows Removed by Filter: 436
31. 144.364 144.364 ↑ 9.1 16,999 1

CTE Scan on yahoo_performance_stats_campaigns_combined yahoo_performance_stats_campaigns_combined_1 (cost=0.00..12,806.92 rows=155,235 width=32) (actual time=2.391..144.364 rows=16,999 loops=1)

  • Filter: (day >= (CURRENT_DATE - '3 days'::interval))
  • Rows Removed by Filter: 459,508
32. 8.169 360.099 ↓ 279.1 18,703 1

Sort (cost=14,754.89..14,755.06 rows=67 width=104) (actual time=358.215..360.099 rows=18,703 loops=1)

  • Sort Key: yahoo_performance_stats90.campaign_id
  • Sort Method: quicksort Memory: 2,230kB
33. 1.751 351.930 ↓ 279.1 18,703 1

Subquery Scan on yahoo_performance_stats90 (cost=14,747.35..14,752.86 rows=67 width=104) (actual time=317.297..351.930 rows=18,703 loops=1)

34. 173.647 350.179 ↓ 279.1 18,703 1

HashAggregate (cost=14,747.35..14,752.19 rows=67 width=104) (actual time=317.296..350.179 rows=18,703 loops=1)

  • Group Key: yahoo_performance_stats_campaigns_combined_2.campaign_id
  • Filter: (round((sum(yahoo_performance_stats_campaigns_combined_2.spend))::numeric, 2) < '100000'::numeric)
35. 176.532 176.532 ↓ 3.1 476,507 1

CTE Scan on yahoo_performance_stats_campaigns_combined yahoo_performance_stats_campaigns_combined_2 (cost=0.00..12,806.92 rows=155,235 width=32) (actual time=0.016..176.532 rows=476,507 loops=1)

  • Filter: (day >= (CURRENT_DATE - '90 days'::interval))