explain.depesz.com

PostgreSQL's explain analyze made readable

Result: II6dK

Settings
# exclusive inclusive rows x rows loops node
1. 24.824 196,783.581 ↓ 8.0 152 1

Hash Join (cost=5,802,712.80..5,802,719.81 rows=19 width=552) (actual time=196,715.019..196,783.581 rows=152 loops=1)

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

CTE yahoo_performance_stats_campaigns_combined

3. 6,630.401 162,034.949 ↓ 1.0 8,421,628 1

Unique (cost=4,283,441.03..4,872,521.27 rows=8,415,432 width=396) (actual time=111,623.138..162,034.949 rows=8,421,628 loops=1)

4. 135,574.191 155,404.548 ↓ 1.0 8,421,628 1

Sort (cost=4,283,441.03..4,304,479.61 rows=8,415,432 width=396) (actual time=111,621.822..155,404.548 rows=8,421,628 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,368kB
5. 739.035 19,830.357 ↓ 1.0 8,421,628 1

Append (cost=0.00..636,747.80 rows=8,415,432 width=396) (actual time=1.110..19,830.357 rows=8,421,628 loops=1)

6. 19,076.786 19,076.786 ↓ 1.0 8,408,565 1

Seq Scan on yahoo_performance_stats_campaigns (cost=0.00..509,186.69 rows=8,402,369 width=321) (actual time=1.109..19,076.786 rows=8,408,565 loops=1)

7. 14.536 14.536 ↑ 1.0 13,063 1

Seq Scan on yahoo_performance_stats_campaigns yahoo_performance_stats_campaigns_1 (cost=0.00..1,329.63 rows=13,063 width=327) (actual time=1.348..14.536 rows=13,063 loops=1)

8.          

CTE yahoo_campaigns_combined

9. 23.146 185.022 ↑ 1.0 33,486 1

Unique (cost=8,977.40..10,400.55 rows=33,486 width=344) (actual time=154.776..185.022 rows=33,486 loops=1)

10. 110.441 161.876 ↑ 1.0 33,486 1

Sort (cost=8,977.40..9,061.11 rows=33,486 width=344) (actual time=154.101..161.876 rows=33,486 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: 7,432kB
11. 4.046 51.435 ↑ 1.0 33,486 1

Append (cost=1,345.13..3,375.46 rows=33,486 width=344) (actual time=28.489..51.435 rows=33,486 loops=1)

12. 16.512 40.705 ↑ 1.0 31,370 1

Seq Scan on yahoo_campaigns yahoo_campaigns_2 (cost=1,345.13..2,690.26 rows=31,370 width=292) (actual time=28.118..40.705 rows=31,370 loops=1)

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

Initplan (for Seq Scan)

14. 10.450 24.193 ↑ 1.0 1 1

Aggregate (cost=1,345.12..1,345.13 rows=1 width=8) (actual time=24.193..24.193 rows=1 loops=1)

15. 13.743 13.743 ↑ 1.0 31,370 1

Seq Scan on yahoo_campaigns (cost=0.00..1,266.70 rows=31,370 width=8) (actual time=0.013..13.743 rows=31,370 loops=1)

16. 2.382 6.684 ↑ 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=5.938..6.684 rows=2,116 loops=1)

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

Initplan (for Seq Scan)

18. 2.274 4.302 ↑ 1.0 1 1

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

19. 2.028 2.028 ↑ 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.365..2.028 rows=2,116 loops=1)

20. 85.211 173,976.617 ↓ 101.0 6,768 1

HashAggregate (cost=306,228.22..306,233.06 rows=67 width=104) (actual time=173,910.319..173,976.617 rows=6,768 loops=1)

  • Group Key: yahoo_performance_stats_campaigns_combined.campaign_id
  • Filter: (round((sum(yahoo_performance_stats_campaigns_combined.spend))::numeric, 2) < '10'::numeric)
  • Rows Removed by Filter: 433
21. 173,891.406 173,891.406 ↑ 55.1 16,966 1

CTE Scan on yahoo_performance_stats_campaigns_combined (cost=0.00..294,540.12 rows=935,048 width=32) (actual time=112,620.802..173,891.406 rows=16,966 loops=1)

  • Filter: ((campaign_start_date <= (CURRENT_DATE - '30 days'::interval)) AND (day >= (CURRENT_DATE - '3 days'::interval)))
  • Rows Removed by Filter: 8,404,662
22. 0.963 22,782.140 ↓ 67.3 404 1

Hash (cost=613,562.68..613,562.68 rows=6 width=296) (actual time=22,782.136..22,782.140 rows=404 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 99kB
23. 5.462 22,781.177 ↓ 67.3 404 1

Merge Join (cost=613,561.44..613,562.68 rows=6 width=296) (actual time=22,770.320..22,781.177 rows=404 loops=1)

  • Merge Cond: (yahoo_campaigns_combined.id = yahoo_performance_stats90.campaign_id)
24. 4.447 11,253.110 ↓ 21.3 404 1

Merge Join (cost=307,325.68..307,326.48 rows=19 width=192) (actual time=11,247.992..11,253.110 rows=404 loops=1)

  • Merge Cond: (yahoo_campaigns_combined.id = yahoo_performance_stats1.campaign_id)
25. 1.349 220.989 ↓ 9.5 533 1

Sort (cost=1,089.92..1,090.06 rows=56 width=88) (actual time=220.894..220.989 rows=533 loops=1)

  • Sort Key: yahoo_campaigns_combined.id
  • Sort Method: quicksort Memory: 166kB
26. 219.640 219.640 ↓ 9.5 533 1

CTE Scan on yahoo_campaigns_combined (cost=0.00..1,088.30 rows=56 width=88) (actual time=157.616..219.640 rows=533 loops=1)

  • Filter: ((effective_status = 'ACTIVE'::text) AND (date(created_date) < (CURRENT_DATE - '30 days'::interval)))
  • Rows Removed by Filter: 32,953
27. 9.853 11,027.674 ↓ 228.0 15,274 1

Sort (cost=306,235.76..306,235.92 rows=67 width=104) (actual time=11,025.490..11,027.674 rows=15,274 loops=1)

  • Sort Key: yahoo_performance_stats1.campaign_id
  • Sort Method: quicksort Memory: 1,600kB
28. 2.781 11,017.821 ↓ 232.3 15,564 1

Subquery Scan on yahoo_performance_stats1 (cost=306,228.22..306,233.73 rows=67 width=104) (actual time=10,967.224..11,017.821 rows=15,564 loops=1)

29. 114.986 11,015.040 ↓ 232.3 15,564 1

HashAggregate (cost=306,228.22..306,233.06 rows=67 width=104) (actual time=10,967.218..11,015.040 rows=15,564 loops=1)

  • Group Key: yahoo_performance_stats_campaigns_combined_1.campaign_id
  • Filter: (round((sum(yahoo_performance_stats_campaigns_combined_1.spend))::numeric, 2) < '500'::numeric)
  • Rows Removed by Filter: 95
30. 10,900.054 10,900.054 ↑ 7.2 129,543 1

CTE Scan on yahoo_performance_stats_campaigns_combined yahoo_performance_stats_campaigns_combined_1 (cost=0.00..294,540.12 rows=935,048 width=32) (actual time=180.199..10,900.054 rows=129,543 loops=1)

  • Filter: ((campaign_start_date <= (CURRENT_DATE - '30 days'::interval)) AND (day >= (CURRENT_DATE - '20 days'::interval)))
  • Rows Removed by Filter: 8,292,085
31. 12.386 11,522.605 ↓ 274.8 18,409 1

Sort (cost=306,235.76..306,235.92 rows=67 width=104) (actual time=11,520.035..11,522.605 rows=18,409 loops=1)

  • Sort Key: yahoo_performance_stats90.campaign_id
  • Sort Method: quicksort Memory: 2,230kB
32. 3.053 11,510.219 ↓ 279.1 18,703 1

Subquery Scan on yahoo_performance_stats90 (cost=306,228.22..306,233.73 rows=67 width=104) (actual time=11,452.231..11,510.219 rows=18,703 loops=1)

33. 293.356 11,507.166 ↓ 279.1 18,703 1

HashAggregate (cost=306,228.22..306,233.06 rows=67 width=104) (actual time=11,451.873..11,507.166 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)
34. 11,213.810 11,213.810 ↑ 2.0 476,474 1

CTE Scan on yahoo_performance_stats_campaigns_combined yahoo_performance_stats_campaigns_combined_2 (cost=0.00..294,540.12 rows=935,048 width=32) (actual time=141.762..11,213.810 rows=476,474 loops=1)

  • Filter: ((campaign_start_date <= (CURRENT_DATE - '30 days'::interval)) AND (day >= (CURRENT_DATE - '90 days'::interval)))
  • Rows Removed by Filter: 7,945,154