explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XScd

Settings
# exclusive inclusive rows x rows loops node
1. 281.355 67,735.952 ↓ 147.0 428,037 1

Sort (cost=8,180,585.68..8,180,587.14 rows=2,911 width=219) (actual time=67,654.430..67,735.952 rows=428,037 loops=1)

  • Sort Key: ads.date DESC
  • Sort Method: quicksort Memory: 72504kB
2.          

CTE base_row_by_sku

3. 910.459 22,635.861 ↑ 1.4 82,864 1

Unique (cost=7,858,181.02..7,863,779.08 rows=116,443 width=103) (actual time=21,111.949..22,635.861 rows=82,864 loops=1)

4. 16,125.120 21,725.402 ↑ 1.1 4,914,655 1

Sort (cost=7,858,181.02..7,860,980.05 rows=5,598,061 width=103) (actual time=21,111.942..21,725.402 rows=4,914,655 loops=1)

  • Sort Key: daily_product_metrics_ongoing_totals.sku, daily_product_metrics_ongoing_totals.date DESC
  • Sort Method: quicksort Memory: 935357kB
5. 4,172.497 5,600.282 ↑ 1.1 4,914,655 1

Bitmap Heap Scan on daily_product_metrics_ongoing_totals (cost=143,176.23..7,732,692.11 rows=5,598,061 width=103) (actual time=1,511.706..5,600.282 rows=4,914,655 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date >= '2019-04-09 00:00:00'::timestamp without time zone) AND (date <= '2019-06-07 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=354060
6. 1,427.785 1,427.785 ↑ 1.1 4,914,655 1

Bitmap Index Scan on dpm_ongoing_totals__sellerid__marketplaceid__date__parent_asin (cost=0.00..142,896.32 rows=5,598,061 width=0) (actual time=1,427.785..1,427.785 rows=4,914,655 loops=1)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date >= '2019-04-09 00:00:00'::timestamp without time zone) AND (date <= '2019-06-07 00:00:00'::timestamp without time zone))
7. 922.931 67,454.597 ↓ 147.0 428,037 1

WindowAgg (cost=316,736.72..316,773.10 rows=2,911 width=219) (actual time=66,501.544..67,454.597 rows=428,037 loops=1)

8. 783.079 66,531.666 ↓ 147.0 428,037 1

Sort (cost=316,736.72..316,738.17 rows=2,911 width=199) (actual time=66,501.425..66,531.666 rows=428,037 loops=1)

  • Sort Key: ads.sku, ads.date
  • Sort Method: quicksort Memory: 94705kB
9. 186.209 65,748.587 ↓ 147.0 428,037 1

Hash Right Join (cost=315,908.49..316,703.22 rows=2,911 width=199) (actual time=64,016.816..65,748.587 rows=428,037 loops=1)

  • Hash Cond: ((base_row.sku)::text = (ads.sku)::text)
10. 22,659.774 22,659.774 ↑ 1.4 82,864 1

CTE Scan on base_row_by_sku base_row (cost=0.00..698.66 rows=116,443 width=84) (actual time=21,111.959..22,659.774 rows=82,864 loops=1)

11. 219.266 42,902.604 ↓ 85,607.4 428,037 1

Hash (cost=315,908.48..315,908.48 rows=5 width=147) (actual time=42,902.604..42,902.604 rows=428,037 loops=1)

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 52571kB
12. 63.093 42,683.338 ↓ 85,607.4 428,037 1

Subquery Scan on ads (cost=315,906.99..315,908.48 rows=5 width=147) (actual time=40,025.422..42,683.338 rows=428,037 loops=1)

13. 2,420.722 42,620.245 ↓ 85,607.4 428,037 1

Hash Right Join (cost=315,906.99..315,908.46 rows=5 width=147) (actual time=40,025.417..42,620.245 rows=428,037 loops=1)

  • Hash Cond: ((pa_1.sku)::text = (p.sku)::text)
  • Join Filter: (pf_1.date = date_range.date)
  • Rows Removed by Join Filter: 12630798
14.          

CTE profile

15. 0.013 0.883 ↑ 1.0 1 1

Limit (cost=0.12..8.13 rows=1 width=8) (actual time=0.870..0.883 rows=1 loops=1)

16. 0.006 0.870 ↑ 1.0 1 1

Nested Loop (cost=0.12..8.13 rows=1 width=8) (actual time=0.869..0.870 rows=1 loops=1)

17. 0.793 0.793 ↑ 1.0 1 1

Index Scan using seller_id_unique on merchants m (cost=0.06..4.06 rows=1 width=4) (actual time=0.793..0.793 rows=1 loops=1)

  • Index Cond: ((seller_id)::text = 'A3TUJE1XKIZPOF'::text)
18. 0.071 0.071 ↑ 1.0 1 1

Index Scan using profiles_merchant_id_index on profiles p_2 (cost=0.06..4.06 rows=1 width=16) (actual time=0.071..0.071 rows=1 loops=1)

  • Index Cond: (merchant_id = m.id)
  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
19.          

CTE date_range

20. 0.079 0.079 ↑ 16.4 61 1

Function Scan on generate_series (cost=0.00..3.00 rows=1,000 width=8) (actual time=0.073..0.079 rows=61 loops=1)

21. 324.014 7,478.250 ↓ 1,694.7 211,842 1

GroupAggregate (cost=156,758.45..156,759.45 rows=125 width=172) (actual time=7,076.576..7,478.250 rows=211,842 loops=1)

  • Group Key: pf_1.date, pa_1.sku
22. 1,112.298 7,154.236 ↓ 3,497.1 437,140 1

Sort (cost=156,758.45..156,758.51 rows=125 width=38) (actual time=7,076.528..7,154.236 rows=437,140 loops=1)

  • Sort Key: pf_1.date, pa_1.sku
  • Sort Method: quicksort Memory: 48676kB
23. 257.246 6,041.938 ↓ 3,497.1 437,140 1

Nested Loop (cost=1,286.85..156,757.58 rows=125 width=38) (actual time=72.868..6,041.938 rows=437,140 loops=1)

24. 49.196 282.822 ↓ 4.9 250,085 1

Nested Loop (cost=1,286.73..93,997.48 rows=50,722 width=40) (actual time=72.513..282.822 rows=250,085 loops=1)

25. 0.007 0.007 ↑ 1.0 1 1

CTE Scan on profile p_1 (cost=0.00..0.01 rows=1 width=8) (actual time=0.003..0.007 rows=1 loops=1)

26. 163.050 233.619 ↓ 4.9 250,085 1

Bitmap Heap Scan on product_ads pa_1 (cost=1,286.73..93,845.31 rows=50,722 width=32) (actual time=72.483..233.619 rows=250,085 loops=1)

  • Recheck Cond: (profile_id = p_1.id)
  • Heap Blocks: exact=12225
27. 70.569 70.569 ↓ 4.9 250,125 1

Bitmap Index Scan on product_ads_profile_sku_campaigns_idx (cost=0.00..1,284.20 rows=50,722 width=0) (actual time=70.569..70.569 rows=250,125 loops=1)

  • Index Cond: (profile_id = p_1.id)
28. 5,501.870 5,501.870 ↓ 2.0 2 250,085

Index Scan using daily_product_ad_performances_product_ad_id_date_pkey on daily_product_ad_performances pf_1 (cost=0.11..1.23 rows=1 width=38) (actual time=0.020..0.022 rows=2 loops=250,085)

  • Index Cond: (product_ad_id = pa_1.id)
  • Filter: (pa_1.profile_id = profile_id)
29. 137.800 32,721.273 ↓ 85,607.4 428,037 1

Hash (cost=159,137.39..159,137.39 rows=5 width=63) (actual time=32,721.273..32,721.273 rows=428,037 loops=1)

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 46458kB
30. 66.003 32,583.473 ↓ 85,607.4 428,037 1

Nested Loop (cost=158,406.34..159,137.39 rows=5 width=63) (actual time=32,100.942..32,583.473 rows=428,037 loops=1)

31. 6.152 32,377.130 ↓ 7,017.0 7,017 1

Nested Loop (cost=158,406.34..159,129.37 rows=1 width=55) (actual time=32,100.855..32,377.130 rows=7,017 loops=1)

32. 180.639 32,103.002 ↓ 19.2 7,052 1

HashAggregate (cost=158,406.23..158,407.33 rows=368 width=16) (actual time=32,100.156..32,103.002 rows=7,052 loops=1)

  • Group Key: (pa.sku)::text
33. 180.304 31,922.363 ↓ 1,091.9 401,803 1

Nested Loop (cost=1,286.85..158,406.05 rows=368 width=16) (actual time=1,300.302..31,922.363 rows=401,803 loops=1)

  • Join Filter: (prof.id = pf.profile_id)
34. 113.294 2,732.199 ↓ 4.9 250,085 1

Nested Loop (cost=1,286.73..93,997.48 rows=50,722 width=40) (actual time=1,294.829..2,732.199 rows=250,085 loops=1)

35. 0.895 0.895 ↑ 1.0 1 1

CTE Scan on profile prof (cost=0.00..0.01 rows=1 width=8) (actual time=0.874..0.895 rows=1 loops=1)

36. 1,327.926 2,618.010 ↓ 4.9 250,085 1

Bitmap Heap Scan on product_ads pa (cost=1,286.73..93,845.31 rows=50,722 width=32) (actual time=1,293.944..2,618.010 rows=250,085 loops=1)

  • Recheck Cond: (profile_id = prof.id)
  • Heap Blocks: exact=12225
37. 1,290.084 1,290.084 ↓ 4.9 250,125 1

Bitmap Index Scan on product_ads_profile_sku_campaigns_idx (cost=0.00..1,284.20 rows=50,722 width=0) (actual time=1,290.084..1,290.084 rows=250,125 loops=1)

  • Index Cond: (profile_id = prof.id)
38. 29,009.860 29,009.860 ↓ 2.0 2 250,085

Index Scan using daily_product_ad_performances_product_ad_id_date_pkey on daily_product_ad_performances pf (cost=0.11..1.27 rows=1 width=16) (actual time=0.106..0.116 rows=2 loops=250,085)

  • Index Cond: ((product_ad_id = pa.id) AND (date >= '2019-06-08'::date) AND (date <= '2019-08-07'::date))
  • Filter: (pa.profile_id = profile_id)
39. 267.976 267.976 ↑ 1.0 1 7,052

Index Scan using products_seller_marketplace_sku_unique on products p (cost=0.11..1.96 rows=1 width=55) (actual time=0.038..0.038 rows=1 loops=7,052)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (pa.sku)::text))
  • Filter: is_valid
  • Rows Removed by Filter: 0
40. 140.340 140.340 ↓ 12.2 61 7,017

CTE Scan on date_range (cost=0.00..8.00 rows=5 width=8) (actual time=0.000..0.020 rows=61 loops=7,017)

  • Filter: (((date)::date >= '2019-06-08'::date) AND ((date)::date <= '2019-08-07'::date))
Planning time : 9.469 ms
Execution time : 67,817.800 ms